Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


records in table A not present in table B


records in table A not present in table B

Author
Message
happy55
happy55
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 76
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
wolfkillj
wolfkillj
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1104 Visits: 2582
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
happy55
happy55
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 76
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
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36152 Visits: 18751
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
My Blog: www.voiceofthedba.com
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8554 Visits: 18142
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
wolfkillj
wolfkillj
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1104 Visits: 2582
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
happy55
happy55
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 76
Yes I had a where clause for table B.I removed it as per your suggestions and it works:-)
Thanks for your help
wolfkillj
wolfkillj
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1104 Visits: 2582
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
wolfkillj
wolfkillj
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1104 Visits: 2582
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
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8554 Visits: 18142
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search