SQL Select subquery

Question

I'm a little new to SQL and have come across the following problem.

I have a table with company details on it which is joined to a contact table by an enqID.

Within the contact table, there are 4 different types of contacts which may or may not have an entry. These are differentiated by a ctcTypID (1 - 4)

I would like to produce a query that has all of the company records on it plus a contact name for each of the 4 different types of contact, all on one row.

I thought I could do this by using subqueries for each of the different contacts but I can't seem to get it to work as I don't know how to write a subquery select clause that references it's parent select clause (if you see what I mean!)

Is this even possible? As I've mentioned, I'm pretty new to SQL so please try not to mock too much!

Thanks,

Steve

Answer 1

I think that you're trying to use SQL for something that it is not designed for. SQL is a language that lets you manipulate and retrieve data, you're trying to use SQL to format the output of the retrieved data as well, and I think that you should not do that:

You should not try to format (putting all contacts on one row f.i.) the data with SQL. My advice is: just use the most performant SQL-query to retrieve the data from the database, and format the output (put the contacts on the same row) in another language (C#, Delphi, PHP, whathever you're using for your application).

Answer 2

Something like (assuming you're using SQL Server 2005 and up - unfortunately, you didn't mention that in your original post):

SELECT 
   c.CompanyName,
   c1.ctcTypID, c1.ContactName,
   c2.ctcTypID, c2.ContactName,
   c3.ctcTypID, c3.ContactName,
   c4.ctcTypID, c4.ContactName
FROM
   CompanyTable c
LEFT OUTER JOIN
   ContactTable c1 ON c.enqID = c1.enqID AND c1.ctcTypID = 1
LEFT OUTER JOIN
   ContactTable c2 ON c.enqID = c2.enqID AND c2.ctcTypID = 2
LEFT OUTER JOIN
   ContactTable c3 ON c.enqID = c3.enqID AND c3.ctcTypID = 3
LEFT OUTER JOIN
   ContactTable c4 ON c.enqID = c4.enqID AND c4.ctcTypID = 4

You need to use LEFT OUTER JOINs since there might not be a match, and by doing this, your query will not be awfully fast in terms of performance - but it should hopefully work.

Marc

Answer 3

This should also work. Avoids joining on the contact table multiple times.

SELECT 
   CompanyTable.CompanyName,
   MAX(CASE WHEN ContactTable.ctcTypID  = 1 THEN ContactTable.ContactName END) AS ContactName1,
   MAX(CASE WHEN ContactTable.ctcTypID  = 2 THEN ContactTable.ContactName END) AS ContactName2,
   MAX(CASE WHEN ContactTable.ctcTypID  = 3 THEN ContactTable.ContactName END) AS ContactName3,
   MAX(CASE WHEN ContactTable.ctcTypID  = 4 THEN ContactTable.ContactName END) AS ContactName4
FROM CompanyTable,
LEFT JOIN ContactTable 
    ON ContactTable.enqID = CompanyTable.enqID AND ContactTable.ctcTypID  IN (1,2,3,4)
GROUP BY
   CompanyTable.CompanyName