October 27, 2008 at 6:36 am
Hi,
I'm having trouble creating a query to perform a function I require. I have created 2 queries which produce the results I want indivividally but I want to combine them into the same query and display the results in a single REPEATER.
SELECT Col1, UserId, Col3, Col4
FROM table1
WHERE (UserId = @userid)
ORDER BY Col3 ASC
------------------------
SELECT Col1,
COUNT(Col3)+COUNT(Col4)+COUNT(Col5)+COUNT(Col6)+COUNT(Col7)
+COUNT(Col8)+COUNT(Col9)+COUNT(Col10)+COUNT(Col11)+COUNT(Col12)
FROM table1
GROUP BY Col3
Is this possible?
Thanks
Mark
October 27, 2008 at 7:13 am
Without knowing anything about your particular situation, I can try to ffer a general answer. There are two ways to "combine": UNION or JOIN.
I'm guessing you want a JOIN here, because your column counts are different, which makes a UNION invalid.
So, this will only work if col1 is some sort of unique value
Select * From
(
SELECT Col1, UserId, Col3, Col4
FROM table1
WHERE (UserId = @userid)
) as FirstTable
INNER JOIN
(
SELECT Col1,
COUNT(Col3)+COUNT(Col4)+COUNT(Col5)+COUNT(Col6)+COUNT(Col7)+COUNT(Col8)+COUNT(Col9)+COUNT(Col10)+COUNT(Col11)+COUNT(Col12)
FROM table1
GROUP BY Col3
) as SecondTable
ORDER BY Col3 ASC
Don't know if this is what you need, but given the limited information, it's the best I can do.
Hope this helps.
October 27, 2008 at 7:31 am
Hi,
That definitely points me in the right direction. If I understand correctly I need to perform the first query, then create a join which will add the results of the second query to the first.
To Explain..I have a repeater control where I wish to display the results of the first query. There will be multiple rows returned as a result of this query.
Within a label control displayed in the relevant repeater row I wish to display the output integer value of the second query. The Group By statement can also be run on Col1 (Primary Key) if that makes a difference.
Does this make it any clearer?
Thanks for your reply.
Mark
October 27, 2008 at 7:38 am
OK, I misunderstood the direction you are going with this. What you are after is a nested data repeater. Googling for "nested data repeater" will get you everything you need I think.
check out
http://www.gridviewguy.com/ArticleDetails.aspx?articleID=185
and
October 27, 2008 at 7:40 am
Can you post what your desired outcome looks like? Also post some test data so we can see exactly what you are working with and trying to do. Take a look at the links in my signature line, they will help you help us help you.
Ultimately you only want ot query the database once and then manipulate the data in your UI. Here is how I'd write the query:
SELECT
T.Col1,
T.UserId,
T.Col3,
T.Col4,
DT.count_sum
FROM
table1 T Join
(
SELECT
Col1,
COUNT(Col3)+COUNT(Col4)+COUNT(Col5)+COUNT(Col6)+COUNT(Col7)+
COUNT(Col8)+COUNT(Col9)+COUNT(Col10)+COUNT(Col11)+COUNT(Col12) as count_sum
FROM
table1
GROUP BY
Col1
) as DT On
T.col1 = DT.col1
WHERE
(T.UserId = @userid)
ORDER BY
T.Col3 ASC
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 27, 2008 at 7:42 am
What are you trying to accomplish with this?
SELECT Col1,
COUNT(Col3)+COUNT(Col4)+COUNT(Col5)+COUNT(Col6)+COUNT(Col7)+COUNT(Col8)+COUNT(Col9)+COUNT(Col10)+COUNT(Col11)+COUNT(Col12)
FROM table1
GROUP BY Col3
That code won't work because of an invalid group by, and your counts probably aren't going to give you what you're looking for.
October 27, 2008 at 8:01 am
Do I really need to nest a repeater control in the main one If I will only have one value displayed in the nested repeater?
I thought it would be simpler than this. I thought the first example you gave seemed like the right way to go so I wonder if I have distracted you with a poor explanation.
I'll try again:
I have a table X
CatId UserId Name Location etc..etc...
My first query is to locate the CatId's I require filtered by a UserId. The results of this query are displayed with the repeater item template.
My second query produces a table which displays all the rows in the table and gives a value "X" which represents the number of non-null cells within the data row (i.e.catId)
So what I'm hoping to produce is a table which will contain a list of CatId's with the value "X" displayed for each CatId which is filtered by a UserId.
Is this clearer?
October 27, 2008 at 8:05 am
Yes, I did get distracted. The nested repeater is definitely the wrong way to go with just one record in the second query. I like Mr. Corbetts solution as posted above. Give it a shot, and let us know how it goes.
October 27, 2008 at 8:05 am
Hi Seth,
My Group by should be on Col1 not Col3 ...thanks for pointing this out. This is fine for my query.
Mark
October 27, 2008 at 10:01 am
That worked beautifully...I'm most impressed and you've certainly earnt your place in the hall of fame.
Thanks a million everbody for helping me with this.
Regards
Mark
October 27, 2008 at 10:50 am
Hi,
I don't want to take the **** but while we're on the subject.....!;)
As part of the same procedure I'm also hoping to add another statement that will query a seperate table (Table2) and return a value which will be the number of rows returned from Table2 when the value of Col1 in Table2 is the same as Col1 in Table1.
Does this make sense?
October 27, 2008 at 10:55 am
Jack Corbett
Can you post what your desired outcome looks like? Also post some test data so we can see exactly what you are working with and trying to do. Take a look at the links in my signature line, they will help you help us help you.
Doing this would make things a lot easier.
October 27, 2008 at 11:23 am
fair enough...
i'll have a look and try to provide some data in the manner stated in the links.
might take me a while though so bear with me.
Thanks again
Mark
October 27, 2008 at 3:39 pm
Here goes,
First time i've done this so I hope it's correct.
CREATE TABLE #PropDet
(
PropId INT IDENTITY(1, 1) PRIMARY KEY,
UserId UNIQUEIDENTIFIER,
PropName NVARCHAR(50),
Resort NVARCHAR(50),
Col5 NVARCHAR(50),
Col6 NVARCHAR(50),
Col7 NVARCHAR(50),
Col8 NVARCHAR(50),
Col9 NVARCHAR(50),
Col10 NVARCHAR(50),
Col11 NVARCHAR(50),
Col12 NVARCHAR(50)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #PropDet ON
INSERT INTO #PropDet
(PropId, UserId, PropName, Resort)
SELECT '6','6EC16A5D-1257-49FA-AA1B-8AC745AF41E5','Villa Marky','Courchevel 1850', UNION ALL
SELECT '9','E3EEAE25-622E-4C6B-B782-FDA576C9B10B','Villa Mummypenny Elly ','Meribel', UNION ALL
SELECT '17','6EC16A5D-1257-49FA-AA1B-8AC745AF41E5','Chateaux La La','Meribel'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #PropDet ON
CREATE TABLE #Rates
(
RowId INT IDENTITY(1, 1) PRIMARY KEY,
PropId INT,
col1 NVARCHAR(50)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #Rates ON
INSERT INTO #Rates
(RowId, PropId, Col1)
SELECT '4','6','March', UNION ALL
SELECT '5','6','April', UNION ALL
SELECT '6','6','January', UNION ALL
SELECT '9','17','January', UNION ALL
SELECT '10','17','February', UNION ALL
SELECT '11','17','March', UNION ALL
SELECT '12','17','April', UNION ALL
SELECT '18','17','May', UNION ALL
SELECT '19','17','June', UNION ALL
SELECT '20','17','July', UNION ALL
SELECT '21','17','August'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #Rates ON
-----------------------------------------------
So as to what I would like to end up with:
A table with columns
PropId, UserId, PropName, Resort, count_sum, columnX
where columnX is the number of rows in table - #Rates with a PropId value corresponding to the PropId in the row of the output table.
Does this make sense?
Thanks again for the help. It's much appreciated.
Mark
October 28, 2008 at 6:40 am
Hi Guys,
This is the additional query I would like to add onto the existing one.
SELECT PropId, COUNT(*) AS [Number of Rows]
FROM Table2
GROUP BY PropId
I've been having a play around trying to incorporate it into the one you kindly supplied but i'm very much a novice at this and can't get it to work.
Any help is much appreciated.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply