SQL Select Subquery
I am a bit new to SQL and faced the following problem.
I have a table with company data on it, which is tied to the contacts table using enqID.
There are 4 different types of contacts in the contact table, which may or may not have an entry. They are differentiated by ctcTypID (1 - 4)
I would like to create a query that contains all the company records, plus the contact name for each of 4 different types of contacts, all on one line.
I thought I could do this using subqueries for each of the different contacts, but I can't get it to work as I don't know how to write a subquery select clause that references its parent select clause (if you understand what I have mean!)
Is it possible? As I mentioned, I'm pretty new to SQL, so please try not to make fun of too much!
Thank,
Steve
source to share
Something like (if you are using SQL Server 2005 and unfortunately you didn't mention it 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 as there might be no match and by doing so your query won't be terribly fast from a performance standpoint, but it will hopefully work.
Mark
source to share
This should work as well. Avoid joining the pins 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
source to share
I think you are trying to use SQL for something that it is not intended to do. SQL is a language that allows you to manipulate and retrieve data, you are also trying to use SQL to format the output of the extracted data, and I think you shouldn't be doing that:
You shouldn't try to format (put all pins on one fi line) data with SQL. My advice: just use the most efficient SQL query to fetch data from the database and format the output (put pins on one line) in another language (C #, Delphi, PHP, whatever you are using for your application).
source to share