December 26, 2013 at 12:13 pm
I have a
Table A
id
description
Table B
uniqueId
name
id
where id in b is same as id in A
I want to count all uniqueId in B for each id in A
I write the below query but it is giving value only if the count >0 in table B for any id(i.e if any value exist)
SELECT
a.id,
isnull(count(b.uniqueid),0)
FROM
Table A a
left outer join Table b on b.id = a.id
Group by a.id
Can anyone help me.
Thanks
December 27, 2013 at 8:41 am
happy55 (12/26/2013)
I have aTable A
id
description
Table B
uniqueId
name
id
where id in b is same as id in A
I want to count all uniqueId in B for each id in A
I write the below query but it is giving value only if the count >0 in table B for any id(i.e if any value exist)
SELECT
a.id,
isnull(count(b.uniqueid),0)
FROM
Table A a
left outer join Table b on b.id = a.id
Group by a.id
Can anyone help me.
Thanks
There must be something more you haven't told us about your data or your requirement. This works for me:
create table #a (id int)
create table #b (id int, uniqueid int)
insert into #a values (1), (2), (3), (4), (5)
insert into #b values (1, 2), (1, 3), (2, 4), (2, 5), (2, 6), (4, 1)
select a.id
,count(b.uniqueid) as n
from #a a
left outer join #b b on a.id = b.id
group by a.id
Result:
idn
12
23
30
41
50
Jason Wolfkill
December 27, 2013 at 9:36 am
thanks for the reply.I am doing the same thing but still i am not geting all rows present in Table a .
I am geting only those for which the count exists.
Any other suggestions
December 27, 2013 at 9:41 am
Seems to work for me as well.
Perhaps you can include more code and setup DDL as wolfkillj did
December 27, 2013 at 9:44 am
Do you have a WHERE clause in your query using a column from table B?
That might be the issue. If you do, change it to the JOIN clause.
December 27, 2013 at 9:48 am
happy55 (12/27/2013)
thanks for the reply.I am doing the same thing but still i am not geting all rows present in Table a .I am geting only those for which the count exists.
Any other suggestions
Well, at the risk of sounding rude, it's simply not possible to get fewer than all rows from Table A in the query you posted (and I re-posted, with the insignificant modification of leaving out a redundant ISNULL()). By definition, SELECT . . . FROM TableA LEFT OUTER JOIN TableB will return all rows from TableA and join to them the rows from TableB that satisfy the join condition(s). Some rows from TableA may be joined to one or many rows from TableB and some may be joined to zero rows from TableB, but ALL rows from TableA will appear in the result. There MUST be some other code at work here.
Jason Wolfkill
December 27, 2013 at 9:50 am
Yes I had a where clause for table B.I removed it as per your suggestions and it works:-)
Thanks for your help
December 27, 2013 at 9:57 am
wolfkillj (12/27/2013)
happy55 (12/27/2013)
thanks for the reply.I am doing the same thing but still i am not geting all rows present in Table a .I am geting only those for which the count exists.
Any other suggestions
Well, at the risk of sounding rude, it's simply not possible to get fewer than all rows from Table A in the query you posted (and I re-posted, with the insignificant modification of leaving out a redundant ISNULL()). By definition, SELECT . . . FROM TableA LEFT OUTER JOIN TableB will return all rows from TableA and join to them the rows from TableB that satisfy the join condition(s). Some rows from TableA may be joined to one or many rows from TableB and some may be joined to zero rows from TableB, but ALL rows from TableA will appear in the result. There MUST be some other code at work here.
A caveat, though - the *number* of rows in the result of the query you posted may be fewer than the *total* number of rows in Table A because of the aggregration. If there are multiple rows in Table A with id = 1, there will only be one row in the result with id = 1 because you are taking a COUNT() with GROUP BY a.id.
If this is happening, your results may not be what you expect because every row in Table A will be joined to every row in Table B that satisfies the join condition. Look at this example, which differs from the original because there are two rows in Table A where id = 1:
create table #a (id int)
create table #b (id int, uniqueid int)
insert into #a values (1), (2), (3), (4), (5), (1)
insert into #b values (1, 2), (1, 3), (2, 4), (2, 5), (2, 6), (4, 1)
select * from #a
select a.id
,count(b.uniqueid) as n
from #a a
left outer join #b b on a.id = b.id
group by a.id
Result:
idn
14
23
30
41
50
As you can see, there are six rows in Table A but only five rows in the output, and although there are only two rows in Table B where id = 1, the COUNT() function gives a result of 4. That's because the two rows in Table A where id = 1 were each joined with the two rows in Table B where id = 1 (two times two is four). If this is happening to you, you are probably deeper in the weeds than you seem to appreciate, and you'd probably better post your *actual* code, some sample data, and the expected result of your query so we can help you out.
Jason Wolfkill
December 27, 2013 at 9:58 am
Luis Cazares (12/27/2013)
Do you have a WHERE clause in your query using a column from table B?That might be the issue. If you do, change it to the JOIN clause.
+1 for the mind reading!
Jason Wolfkill
December 27, 2013 at 10:02 am
happy55 (12/27/2013)
Yes I had a where clause for table B.I removed it as per your suggestions and it works:-)Thanks for your help
I've seen it a lot of times and it surprised me the first time I had this issue. The conditions on table b transformed your outer join into an inner join.
December 27, 2013 at 10:26 am
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply