SQL - Retrieving Same Table Twice

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

General SQL Server Forums

New to SQL Server Programming

SQL - Retrieving Same Table Twice

Author

Topic

mellamokb
Starting Member

39 Posts

Posted - 2006-01-05 : 17:50:10

Hi,

I have a giant SQL query that retrieves a bunch of information from a couple of tables for use on a page, and I would like some help on constructing the SQL to get exactly what I have in mind. I have a table - called scContacts - that contains a list of contacts with detailed information. The contacts in the table are regular sales contacts, sales reps, and sales managers. Another table - called scCompany - contains a list of companies with detailed information.

For each company, we have a certain sales manager (or user) pertaining to that company as well as potentially a certain rep. The problem is that the contact tied to the company, the sales manager tied to the company, and the rep tied to the company all come from the same table. Here is the base SQL code I've used to get everything but the sales rep's name and sales manager's name:

----- SQL CODE -----
SELECT DISTINCT
scContacts.ID,
scContacts.inheritCoAddr,
scContacts.fName,
scContacts.lName,
scContacts.contact,
scContacts.billToAddress1,
scContacts.billToAddress2,
scContacts.billToState,
scContacts.billToZip,
scContacts.billToCity,
scContacts.billToCounty,
scContacts.billToCountry,

scCompany.ID AS companyID,
scCompany.companyName,
scCompany.companyURL,
scCompany.billToAddress1 AS companyAddress1,
scCompany.billToAddress2 AS companyAddress2,
scCompany.billToCity AS companyCity,
scCompany.billToState AS companyState,
scCompany.billToZip AS companyZip,
scCompany.billToCounty AS companyCounty,
scCompany.billToCountry AS companyCountry,
scCompany.businessType,
scCompany.phoneExt,
scCompany.phoneNum,
scCompany.faxNum,
scCompany.minEmployees,
scCompany.maxEmployees,
scCompany.actionTypeMAX,
scCompany.actionDateMAX,
scCompany.actionTimeMAX,
scCompany.statusID,
scCompany.userID,
scCompany.repID

FROM
scCompany,
scContacts

INNER JOIN
scStatus ON
scStatus.ID = scCompany.statusID

WHERE
scgroupContacts.contactID = [insert cookie value]
AND
scgroups.ID = [insert cookie value]
AND
scCompany.ID = scContacts.companyID
AND
scCompany.groupID = scgroups.ID
----- END SQL CODE -----

As indicated right now, this SQL code will retrieve all of the contact and company information for a certain contact ID which comes from a cookie value (inserted in ASP). I want to get the [fName] and [lName] fields from scContacts table where the scContacts.ID = scCompany.userID, and I also want those same fields from scContacts table again where the scContacts.ID = scCompany.repID. It would be simplest and most efficient if I could do this all at once (and I'm sure it's possible). How would I change the SQL to bring in that information from the same table two more times, tying their ID's to ID's in the company table?

thanks,
mellamokb

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-05 : 21:02:37

you can do this
select *
from scContacts c1, scContacts c2 ...
where c1.ID = ...
and c2.ID = ...

-----------------
[KH]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-06 : 00:09:51

If not, post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

mellamokb
Starting Member

39 Posts

Posted - 2006-01-06 : 17:48:07

Hi,

The following SQL performs fine in the SQL Query Analyzer, and I'm pretty sure it returns what I wanted for the reps (I haven't implemented users yet). Should this work fine? Or is my logic in correct (in reference to my previous post about what I was trying to achieve)?

----- SQL CODE -----
SELECT DISTINCT
scContacts.ID,
scContacts.inheritCoAddr,
scContacts.fName,
scContacts.lName,
scContacts.contact,
scContacts.billToAddress1,
scContacts.billToAddress2,
scContacts.billToState,
scContacts.billToZip,
scContacts.billToCity,
scContacts.billToCounty,
scContacts.billToCountry,

scCompany.ID AS companyID,
scCompany.companyName,
scCompany.companyURL,
scCompany.billToAddress1 AS companyAddress1,
scCompany.billToAddress2 AS companyAddress2,
scCompany.billToCity AS companyCity,
scCompany.billToState AS companyState,
scCompany.billToZip AS companyZip,
scCompany.billToCounty AS companyCounty,
scCompany.billToCountry AS companyCountry,
scCompany.businessType,
scCompany.phoneExt,
scCompany.phoneNum,
scCompany.faxNum,
scCompany.minEmployees,
scCompany.maxEmployees,
scCompany.actionTypeMAX,
scCompany.actionDateMAX,
scCompany.actionTimeMAX,
scCompany.statusID,

scReps.fName AS repsFName,
scReps.lName AS repsLName

FROM
scContacts

LEFT JOIN
scGroupContacts
ON
scGroupContacts.contactID = 78749

LEFT JOIN
scGroups
ON
scGroups.ID = 2619

LEFT JOIN
scCompany
ON
scCompany.groupID = scGroups.ID

LEFT JOIN
scContacts scReps
ON
scReps.ID = scCompany.repID

WHERE
scCompany.ID = scContacts.companyID

ORDER BY
scContacts.ID
----- END SQL CODE -----

thanks,
mellamokb

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources