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.
| Author |
Topic |
|
Webskater
Starting Member
19 Posts |
 Posted - 2008-09-24 : 09:14:29
|
| SELECT *FROM Responders INNER JOIN tblCategory ON Responders.CategoryID = tblCategory.CategoryID LEFT OUTER JOIN tblLocation INNER JOIN tblOrganisation ON tblLocation.OrganisationID = tblOrganisation.OrganisationID ON Responders.LocConID = tblLocation.LocationID LEFT OUTER JOIN tblContact ON Responders.ContactID = tblContact.ContactID LEFT OUTER JOIN ResponderNotes ON Responders.CRID = ResponderNotes.CRID LEFT OUTER JOIN tblContactLocation ON Responders.ContactID = tblContactLocation.ContactID AND Responders.LocConID = tblContactLocation.LocationID LEFT OUTER JOIN tblResponderExtraInfo ON Responders.ContactID = tblResponderExtraInfo.ContactID LEFT OUTER JOIN tblResponderExtraInfoType ON tblResponderExtraInfo.TypeID = tblResponderExtraInfoType.TypeID AND tblResponderExtraInfoType.Type = 'Email'The query above returns data about contacts kept in a table called Responders. Each contact may, or may not, be associated with an organisation and/or a location for that organisation - may or may not have notes etc. Hence all the left joins.The table tblResponderExtraInfo is a list of contact info for a contact. It might have rows like:ContactID Info TypeID1 0123 654789 11 fred@hotmail.com 21 fred@yahoo.com 2Which is basically saying that someone called fred has a phone number and two email addresses stored in the table.In the query I want to return an email address for him - but I only want to return one record for each contact. And, at the moment, the query is returning two rows for fred.How can I restrict the join to the tblResponderExtraInfo table so that, even if contains 3 emaail addresses for someone, the main query only returns one row for that person. (I don't mind which email address I get back - any of them will do.)Thanks for any help. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-24 : 10:46:29
|
May be this:-SELECT other columns,tmp.EmailAddressFROM RespondersINNER JOIN tblCategory ON Responders.CategoryID = tblCategory.CategoryIDLEFT OUTER JOIN tblLocationINNER JOIN tblOrganisation ON tblLocation.OrganisationID = tblOrganisation.OrganisationID ON Responders.LocConID = tblLocation.LocationIDLEFT OUTER JOIN tblContact ON Responders.ContactID = tblContact.ContactIDLEFT OUTER JOIN ResponderNotes ON Responders.CRID = ResponderNotes.CRIDLEFT OUTER JOIN tblContactLocation ON Responders.ContactID = tblContactLocation.ContactID AND Responders.LocConID = tblContactLocation.LocationIDLEFT OUTER JOIN (SELECT ContactID,MAX(Info) AS EmailAddressFROM tblResponderExtraInfo INNER JOIN tblResponderExtraInfoType ON tblResponderExtraInfo.TypeID = tblResponderExtraInfoType.TypeIDAND tblResponderExtraInfoType.Type = 'Email'GROUP BY ContactID) tmpON Responders.ContactID = tmp.ContactID |
 |
|
|
Webskater
Starting Member
19 Posts |
Posted - 2008-09-24 : 11:19:20
|
| Thank you VERY MUCH!That worked perfectly. I had spent hours doing things like that - but, unfortunately, had reached the end of my limited brain power.Thanks again. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-24 : 11:30:03
|
quote:
Originally posted by Webskater
Thank you VERY MUCH!That worked perfectly. I had spent hours doing things like that - but, unfortunately, had reached the end of my limited brain power.Thanks again.
No worries. You're welcome  Glad that i could help. |
|
|
|
|
|
|
|
|
How to restrict records from many side to one?