Joins

Question

Post reply

Joins

Andrew Collins

Ten Centuries

Points: 1369
More actions
December 16, 2005 at 5:53 am

#90249

I have a table which has about 500 unique records all with a unique id (T1) I need to join this to another table which has mutiple occurances of the unique id (T2)
T1 is names and addresses with a unique number
(id,na,a1,a2,tow,co,pc)
T2 contains contact details for the company.(more than one record for each company)
(id,firstname,surname, email)

How do i join the two tables so i get 500 records from T1 and a single contact from T2.
I have tried various joins but i keep getting duplicate rows, because its returning all the contacts.

Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

You are bound to get more than 500 rows as there is a one to many relationship between the tables.

In order to bring back one contact only, use a max() function on the highest value you want to see for the contact.

E.G max(name) will return the contact with the highest alphabetical name ....etc

------------------------------
The Users are always right - when I'm not wrong!

David Burrows SSC Guru Points: 65143 More actions · Answer 2

In order to bring back one contact only, use a max() function on the highest value you want to see for the contact

This will only work if T2 has it's own unique id, as described the data could look like this

T1

1,'CompA'

2,'CompB'

T2

1,'John','Smith','j.smith@compa.com'

1,'Arthur','Jones','a.jones@compa.com'

1,'Alan','Bloggs','a.bloggs@compa.com'

2,'James','Allen','j.allen@compb.com'

2,'Mike','Smith','m.smith@compb.com'

Can you define the tables more accurately and include some (non sensitive) test data and what results you would like.

If the data is as shown above then which contact do you require or do you want a random one?

Far away is close at hand in the images of elsewhere.
Anon.

Andrew Collins Ten Centuries Points: 1369 More actions · Answer 3

the data is as above, it doesn't matter what contact is returned.

thanks

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

So you basically want one contact per company, right?

K. Brian Kelley
@kbriankelley

Andrew Collins Ten Centuries Points: 1369 More actions · Answer 5

yes, all 500 companies with one contact (any contact doesn't matter which one).

thanks

David Burrows SSC Guru Points: 65143 More actions · Answer 6

OK first off the top of my head and providing there are no duplicate contacts!

SELECT T1.*,T2.*

FROM T1

INNER JOIN (SELECT a.[id],MIN(a.firstname+a.surname+a.email) AS FROM T2 a GROUP BY a.[id]) b

ON b.[id] = T1.[id]

INNER JOIN T2

ON T2.[id] = b.[id]

AND T2.firstname+T2.surname+T2.email = b.

better solution is to add unique contactid column to contact and use max() on it

Far away is close at hand in the images of elsewhere.
Anon.

Andrew Collins Ten Centuries Points: 1369 More actions · Answer 7

Thank you very much, that solution is perfect for what i need, as T1 needs to be replaced by differnet tables, so all i need to do is replace the table name and it will work for any table with that layout.

Thansk again

Andrew

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 8

You are actually using T2.firstname+T2.surname+T2.email as a unique id...

David Burrows SSC Guru Points: 65143 More actions · Answer 9

You are actually using T2.firstname+T2.surname+T2.email as a unique id

WOW! I surpassed even myself

Far away is close at hand in the images of elsewhere.
Anon.