How to create slq to return the rows only from the first table with multiple table join?

Question

How to create slq to return the rows only from the first table with multiple table join?

Seattlemsp

SSC Eights!

Points: 932
More actions
July 25, 2012 at 8:18 pm

#392832

Have a 3 table
Tabel A
emplyee_ID, first_name, last_name
1 John Anderson
2 Mary Anderson
3 Tim Silver
Table B
emplyee_ID, address
1 City1
2 City2
3 City3
4 City 4
Table C
emplyee_ID, zip
1 55555
2 66666
3 77777
4 88888
Here is the query I have
Select a.first_name, a.last_name, b.city c.zip
from a join b on a.emplyee_ID = b.emplyee_ID join c on a.emplyee_ID= c.emply_ID
Where a.last name = 'anderson'
What I expect the return should like this:
John Anderson, City1, 55555
Mary Anderson, City2, 66666
However I received more duplicate. Even I change the qurey as below, I still reciev the duplicates
Select distinct a.emplyee_ID, a.first_name, a.last_name, b.city c.zip
from a join b on a.emplyee_ID = b.emplyee_ID join c on a.emplyee_ID= c.emply_ID
Where a.last name = 'anderson'
Thanks a lot for your help!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

demonfox SSCertifiable Points: 6289 More actions · Answer 1

could you post the output that you are getting ?

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Seattlemsp SSC Eights! Points: 932 More actions · Answer 2

Here is what I received:

John Anderson, City1, 55555

John Anderson, City2, 66666

John Anderson, City1, 77777

John Anderson, City2, 88888

Mary Anderson, City1, 55555

Mary Anderson, City2, 66666

Mary Anderson, City1, 77777

Mary Anderson, City2, 88888

Thanks!

demonfox SSCertifiable Points: 6289 More actions · Answer 3

I ran the same query it gives the expected result..

Select a.t, a.tl, b.addr, c.zip

from a join b on a.ID = b.ID

join c

on a.ID= c.ID

Where a.tl = 'anderson'

ttladdrzip

JohnAndersoncity155555

MaryAndersoncity266666

for your result the distinct won't resolve anything, as distinct regards a selected output row(all columns included) to be distinct.

for Join it works as inner join in sql server, so if your table doesn't have duplicate records for an employee id , it should give the expected result.

I am not sure how are you getting the result..

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

subbu1 Hall of Fame Points: 3281 More actions · Answer 4

in third table actual your data same id have multiple zip code.

could you please check it third table have unique id's are not..

Divine Flame SSCoach Points: 15943 More actions · Answer 5

Ensure that EmployeeId is unique in every table. If it is not unique you are bound to get the duplicate records.

Sujeet Singh

Seattlemsp SSC Eights! Points: 932 More actions · Answer 6

You are all correct!

The third table has duplics.

If the third table has duplics, is that possible eliminated the duplic return?

Sorry, my initial information didn't provide the accurate situation.

Thanks,

Divine Flame SSCoach Points: 15943 More actions · Answer 7

Seattlemsp (7/26/2012)
You are all correct!
The third table has duplics.
If the third table has duplics, is that possible eliminated the duplic return?
Sorry, my initial information didn't provide the accurate situation.
Thanks,

The third table contains zip codes, whereas second table has the employeeId & his address (name of the city). Logically you should have a table Cities in your database in which you have the mapping between city & its zip code.

if you have that, you should then pick the right zip code from the Cities table based on the correct address of the employee taken from second table in your example.

Sujeet Singh