Trouble retrieving from multiple tables

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

SQL Server 2005 Forums

Transact-SQL (2005)

Trouble retrieving from multiple tables

Author

Topic

bbowser
Starting Member

43 Posts

Posted - 2009-09-16 : 15:50:53

I have several tables tblContacts that contains all the normal business information, tblContactPerson that has contact information for people who belong to a business and these people are capable of having a single or multiple functions. I need to retrieve the business contact information from the tblContacts table and the Contact Person information from the tblContactPerson table that only has a personfunctionID = '11'. What am I doing wrong? It is returning every contact person information. I've tried a full join to no avail. Here's my statement. Thanks in advance.

SELECT tblContacts.BusinessName, tblContacts.Phone, tblContacts.Fax, tblContacts.Email, tblContacts.Address1, tblContacts.City, tblContacts.State, tblContacts.Zip,
tblContactPerson.Fname
FROM tblPersonToFunction INNER JOIN
tblContactPerson ON tblPersonToFunction.ContactPersonID = tblContactPerson.ContactPersonID RIGHT OUTER JOIN
tblContacts INNER JOIN
tblContactToCounty ON tblContacts.ContactID = tblContactToCounty.ContactID INNER JOIN
tblContactToFunction ON tblContacts.ContactID = tblContactToFunction.ContactID ON tblContactPerson.ParentID = tblContacts.ContactID
WHERE (tblContactToFunction.BusinessFunctionID = '9') AND (tblContactToCounty.CountyID = @County)
ORDER BY tblContacts.BusinessName

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-09-16 : 16:40:09

I just went through the code and corrected the basic problems, I didn't go into your query design.

Try this, if it works great, if not explain more what you need


SELECT 
  c.BusinessName
, c.Phone
, c.Fax
, c.Email
, c.Address1
, c.City
, c.State
, c.Zip, 
, b.Fname
FROM 
	tblPersonToFunction a
inner join
	tblContactPerson b 
ON		a.ContactPersonID = b.ContactPersonID 
left join
	tblContacts  c
on		b.ContactPersonID = c.ContactPersonID
and		b.ParentID = c.ContactID
left join
	tblContactToCounty d
ON		c.ContactID = d.ContactID 
left join
	tblContactToFunction e
ON		d.ContactID = e.ContactID 
WHERE 
		a.BusinessFunctionID = '9' 
AND		d.CountyID = @County)
ORDER BY	c.BusinessName

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

bbowser
Starting Member

43 Posts

Posted - 2009-09-16 : 17:15:39

My appologizes, I should have put this in addition to the Where clause

WHERE (tblContactToFunction.BusinessFunctionID = '9') AND (tblContactToCounty.CountyID = @County) and (tblPersonToFunction.PersonFunctionID = '11')

I just want the companies that have a certain functionID # from the tblContactToFunction table but I also want to retrieve the employees associated with a company that are in a seperate table and that have a PersonFunctionID # of 11 associated with them i.e. just the Bank "President" which might have functionID of 1 or "CFO" which might have a functionID of 6. Does that help explain it. However if the business doesn't have a CFO I still want the business information to show up.

bbowser
Starting Member

43 Posts

Posted - 2009-09-17 : 15:13:02

Let me try a different approach.
The tables:
tblContactToCounty tblContacts tblContactToFunction tblContactPerson tblPersonToFunction
ctcID - PK ContactID - PK ctfID – PK ContactPersonID – PK ptfID - PK
ContactID BusinessName ContactID ParentID ContactPersonID
CountyID Phone BusinessFunctionID Fnam PersonFunctionID
Fax Lname
Address1
City
State
Zip
The ContactID fields are all in relationship with one another. The tblPersonToFunction and tblContactPerson are tied together by the ContactPersonID field and the tblContactPerson ParentID field is tied to the tblContacts ContactID field.
I need to retrieve BusinessName, Phone, Address1, City, State, Zip from tblContacts where the BusinessFunctionID = '9' and the tblContactCounty = @County (variable based on county pulldown list) but I also need to retrieve the Fname and Lname from the tblContactPerson where the PersonFunctionID = '11'. My problem is that I'm confused as to how to do this without retrieving all the records from both the tblContacts table and the tblContactPerson table. I want all the tblContacts that have a BusinessFunctionID of '9' even if they don't have a record in the tblContactPerson table.

bbowser
Starting Member

43 Posts

Posted - 2009-09-18 : 10:33:43

Fixed my own problem. Solution was to put a nested Select statement in the From clause

SELECT tblContacts.BusinessName, tblContacts.Phone, tblContacts.Fax, tblContacts.Email, tblContacts.Address1, tblContacts.City, tblContacts.State, tblContacts.Zip,
derivedtbl_1.Fname + ' ' + derivedtbl_1.Lname AS Contact
FROM (SELECT tblContactPerson.Fname, tblContactPerson.Lname, tblContactPerson.ParentID
FROM tblPersonToFunction INNER JOIN
tblContactPerson ON tblPersonToFunction.ContactPersonID = tblContactPerson.ContactPersonID
WHERE (tblPersonToFunction.PersonFunctionID = '11')) AS derivedtbl_1 RIGHT OUTER JOIN
tblContacts INNER JOIN
tblContactToFunction ON tblContacts.ContactID = tblContactToFunction.ContactID INNER JOIN
tblContactToCounty ON tblContacts.ContactID = tblContactToCounty.ContactID ON derivedtbl_1.ParentID = tblContacts.ContactID
WHERE (tblContactToFunction.BusinessFunctionID = '9') AND (tblContactToCounty.CountyID = @County)
ORDER BY tblContacts.BusinessName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources