SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to restrict records from many side to one?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Webskater
Starting Member

19 Posts

Posted - 09/24/2008 :  09:14:29  Show Profile  Reply with Quote
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 TypeID
1 0123 654789 1
1 fred@hotmail.com 2
1 fred@yahoo.com 2

Which 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

India
52317 Posts

Posted - 09/24/2008 :  10:46:29  Show Profile  Reply with Quote
May be this:-
SELECT other columns,tmp.EmailAddress
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 
(
SELECT ContactID,MAX(Info) AS EmailAddress
FROM tblResponderExtraInfo 
INNER JOIN tblResponderExtraInfoType 
ON tblResponderExtraInfo.TypeID = tblResponderExtraInfoType.TypeID
AND tblResponderExtraInfoType.Type = 'Email'
GROUP BY ContactID) tmp
ON Responders.ContactID = tmp.ContactID
Go to Top of Page

Webskater
Starting Member

19 Posts

Posted - 09/24/2008 :  11:19:20  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/24/2008 :  11:30:03  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000