problem,left join, distinct and duplicate recrods

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

General SQL Server Forums

New to SQL Server Programming

problem,left join, distinct and duplicate recrods

Author

Topic

jaleel
Starting Member

2 Posts

Posted - 2010-12-28 : 01:26:58

how can get correct result
"for more than two tables"

tableA has some people records
pid,pname,address,cityid,tel,eductid

tableB has some city record
cityid, cityname
example : 0 city1
: 1 city2
: 2 city3
: 3 city4

tablec has some education records
eductid, eductstat
example : 0 no education
: 1 primary
: 2 etc.
: 3 etc.
etc.

following code wont help

SELECT tableA.pname ,tableA.tel,tableA.address,tableB.cityname,tableC.eductstat
FROM tableA LEFT OUTER JOIN
tableB ON tableA.cityid= tableB.cityid
LEFT OUTER JOIN tableC ON tableA.eductid = tableC.eductid
ORDER BY tableA.pid

also this one

SELECT tableA.pname ,tableA.tel,tableA.address,tableB1.cityname,tableC1.eductstat
FROM tableA LEFT OUTER JOIN
(select * FROM tableB) as tableB1 ON tableA.cityid= tableB1.cityid
LEFT OUTER JOIN (SELECT * FROM tableC) AS tableC1 ON tableA.eductid = tableC1.eductid
ORDER BY tableA.pid

result has duplicate records

with using DISTINCT

SELECT DISTINCT tableA.pname ,tableA.tel,tableA.address,tableB1.cityname,tableC1.eductstat
FROM tableA LEFT OUTER JOIN
(select * FROM tableB) as tableB1 ON tableA.cityid= tableB1.cityid
LEFT OUTER JOIN (SELECT * FROM tableC) AS tableC1 ON tableA.eductid = tableC1.eductid
ORDER BY tableA.pid

if excluding tableB1.cityname,tableC1.eductstat work, othewise will not work
any suggestion ?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-28 : 01:33:49

Seems like table B has duplicate entries for cityid.
You should clean that if possible.

Check table b with this:
select cityid,count(*) from tableB group by cityid having count(*) > 1
to see the duplicates.

No, you're never too old to Yak'n'Roll if you're too young to die.

jaleel
Starting Member

2 Posts

Posted - 2010-12-28 : 01:38:59

quote:
Originally posted by webfred

Seems like table B has duplicate entries for cityid.
You should clean that if possible.

Check table b with this:
select cityid,count(*) from tableB group by cityid having count(*) > 1
to see the duplicates.

No, you're never too old to Yak'n'Roll if you're too young to die.

thank you for your reply
no, tableA, tableB and tableC have not duplicate records they are all unique

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-28 : 04:49:11

Then your first posted SELECT statement should give you the wanted result.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources