duplicates with UNION and LEFT JOIN

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

duplicates with UNION and LEFT JOIN

Author

Topic

dmaxj
Posting Yak Master

174 Posts

Posted - 2011-04-20 : 13:10:49

I have a total of 4 tables - A,B,C,D. Tables B,C,D all have the same structure (fkID, MeetingDate, MeetingType, AttendStatus). Table A contains a primary key with the names of people (pkID, firstName, lastName).

The people in Table A are exclusively assigned to one of the 3 tables (B,C,D). This means that pkID only appears as a fkID in one of the 3 tables.

My query gives me duplicates whenever someone's ID appears in Table B,C,or D. Here is the sample output:


------------------------------------------------------------------
pkID   MeetingType   AttendStatus   CCount   firstName   lastName
1                                   523      John        Doe
1      ProfCCC       1              523      John        Doe
2                                   523      Kent        Smart
2      ProfAAA       1              523      Kent        Smart
3                                   523      Jane        Smith
4                                   523      Eva         Slaww
------------------------------------------------------------------

Notice that people that are not assigned to any table B, C, D (3 &4) - do not have duplicates. My query follows:


SELECT DISTINCT(pkPatientID), B.MeetingType, B.AttendStatus, COUNT(pkPatientID), firstName, LastName 
     FROM A  LEFT JOIN B ON
     A.pkPatientID = B.fkPatientID  
UNION

SELECT DISTINCT(pkPatientID), C.MeetingType, C.AttendStatus, COUNT(pkPatientID), firstName, LastName 
     FROM A  LEFT JOIN C ON
     A.pkPatientID = C.fkPatientID  
UNION

SELECT DISTINCT(pkPatientID), D.MeetingType, D.AttendStatus, COUNT(pkPatientID), firstName, LastName 
     FROM A  LEFT JOIN D ON
     A.pkPatientID = D.fkPatientID

Regards

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-20 : 13:35:54

use inner joins instead of outer joins

Jim

Everyday I learn something that somebody else already knew

dmaxj
Posting Yak Master

174 Posts

Posted - 2011-04-20 : 13:39:16

Thanks, jimf, but I still want the people from Table A in the result set, even if they are not assigned to table B,C or D.

How can I still get all of the persons in Table A in the result set?

Regards

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-20 : 14:15:53

[code]SELECT a.pkPatientID, t1.MeetingType, t1.AttendStatus, COUNT(pkPatientID), a.firstName, a.LastName
FROM A
LEFT JOIN

(
select distinct b.fkpatientid , b.meetingtype, b.attendstatus
from b
select distinct fkpatientid , c.meetingtype, c.attendstatus
from c
union
select distinct fkpatientid , d.meetingtype, d.attendstatus
from d
) t1

ON A.pkPatientID = t1.fkPatientID
GROUP BY a.pkPatientID, t1.MeetingType, t1.AttendStatus a.firstName, a.LastName [/code]

Jim

Everyday I learn something that somebody else already knew

dmaxj
Posting Yak Master

174 Posts

Posted - 2011-04-20 : 17:08:24

Thanks, jimf - it works.

However, I am curious as to why my original query gave me duplicates only on those people that were assigned to to table B,C, or D ( pkID is a foreign key in table B,C, or D ) - Any thoughts? I am apparently having a logic problem with my original query.

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-21 : 07:01:26

The left join says "Give me everything in TableA, even if it doesn't find a match". So if TableA has PatientIDs 1-10, and TableB has IDS 1-3,C has 4-6 and D has 7-9, each of your queries will return all 10 rows from Table, with only the columns from the other tables where a match was being populated. My query says, "Get all the matches for so I don't get dups, and then add back in the ones where there was no match".

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources