Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

records in table A not present in table B Expand / Collapse
Author
Message
Posted Thursday, December 26, 2013 12:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 1:17 PM
Points: 15, Visits: 38
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
Post #1526018
Posted Friday, December 27, 2013 8:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:05 AM
Points: 1,222, Visits: 2,545
happy55 (12/26/2013)
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


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:
id n
1 2
2 3
3 0
4 1
5 0



Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1526174
Posted Friday, December 27, 2013 9:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 1:17 PM
Points: 15, Visits: 38
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
Post #1526202
Posted Friday, December 27, 2013 9:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:53 PM
Points: 33,204, Visits: 15,353
Seems to work for me as well.

Perhaps you can include more code and setup DDL as wolfkillj did








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1526205
Posted Friday, December 27, 2013 9:43 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 3,545, Visits: 7,659
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.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1526206
Posted Friday, December 27, 2013 9:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:05 AM
Points: 1,222, Visits: 2,545
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
Blog: SQLSouth
Twitter: @SQLSouth
Post #1526208
Posted Friday, December 27, 2013 9:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 1:17 PM
Points: 15, Visits: 38
Yes I had a where clause for table B.I removed it as per your suggestions and it works
Thanks for your help
Post #1526210
Posted Friday, December 27, 2013 9:57 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:05 AM
Points: 1,222, Visits: 2,545
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:
id n
1 4
2 3
3 0
4 1
5 0


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
Blog: SQLSouth
Twitter: @SQLSouth
Post #1526213
Posted Friday, December 27, 2013 9:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:05 AM
Points: 1,222, Visits: 2,545
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
Blog: SQLSouth
Twitter: @SQLSouth
Post #1526214
Posted Friday, December 27, 2013 10:02 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 3,545, Visits: 7,659
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.
Here's a nice article on it: Fun with Outer Joins



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1526217
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse