CODEWITHSUNDEEP
sqlselect
user1389971
user1389971

Reputation: 107

Multiple Select Statements in SQL queries

I admit to being an absolute SQL Noob here (I can do inner joins but that's about as "complicated" as my knowledge goes with SQL), but I am hoping for some help/insight/ideas on how to best achieve something. I have a table of companies, and a subtable of individuals for those companies. The company can have a mailing address, but it is possible that an individual would have a different mailing address to override the normal company address. I have SQL server 2008 in my environment. My thought was to have the SQL query loop through twice in the following manner

SELECT tblIndividuals.FirstName,
       tblIndividuals.LastName,
       tblIndividuals.CompanyName,
       tblIndividuals.MailingAddress1,
       tblIndividuals.MailingAddress2,
       tblIndividuals.MailingAddress3,
       tblIndividuals.MailingAddress4,
       tblIndividuals.City,
       tblIndividuals.[State],
       tblIndividuals.Zip
FROM tblIndividuals
INNER JOIN tblCompanies ON tblIndividuals.CompanyName = tblCompanies.CompanyName
WHERE tblIndividuals.ChristmasList=1
  AND tblIndividuals.MailingAddress1 IS NOT NULL
  SELECT tblIndividuals.FirstName,
         tblIndividuals.LastName,
         tblIndividuals.CompanyName,
         tblCompanies.MailingAddress1,
         tblCompanies.MailingAddress2,
         tblCompanies.MailingAddress3,
         tblCompanies.MailingAddress4,
         tblCompanies.City,
         tblCompanies.[State],
         tblCompanies.Zip
  FROM tblIndividuals
  INNER JOIN tblCompanies ON tblIndividuals.CompanyName = tblCompanies.CompanyName WHERE tblIndividuals.ChristmasList=1
  AND tblIndividuals.MailingAddress1 IS NULL
ORDER BY tblIndividuals.CompanyName

The thought was that this way the code would loop through once, grabbing all of the individuals on the Christmas List that have a mailing address that overrides the company address, then loop through grabbing all of the individuals on the Christmas List that use the default company address, finally sorting all of the results by company name. Those of you much more well versed in SQL than I am know that this doesn't work as I hoped, instead just running two queries. Would any one much more well versed in SQL be willing to lend some insight here in a better way to achieve my ultimate goal? Any help would be greatly appreciated. Thank you for your time in advance.

edit: I know the first query doesn't really need the inner join, I just had copied/pasted from the second query and didn't change it. >.<

Upvotes: 3

Views: 6930

Answers (3)

ristonj
ristonj

Reputation: 1608

select tblIndividuals.FirstName, tblIndividuals.LastName, tblIndividuals.CompanyName, 
COALESCE(tblIndividuals.MailingAddress1, tblCompanies.MailingAddress1) AS MailingAddress1, 
CASE WHEN tblIndividuals.MailingAddress1 IS NULL THEN tblCompanies.MailingAddress2 ELSE tblIndividuals.MailingAddress2 END AS MailingAddress2, 
CASE WHEN tblIndividuals.MailingAddress1 IS NULL THEN tblCompanies.MailingAddress3 ELSE tblIndividuals.MailingAddress3 END AS MailingAddress3, 
CASE WHEN tblIndividuals.MailingAddress1 IS NULL THEN tblCompanies.MailingAddress4 ELSE tblIndividuals.MailingAddress4 END AS MailingAddress4, 
CASE WHEN tblIndividuals.MailingAddress1 IS NULL THEN tblCompanies.City ELSE tblIndividuals.City END AS City, 
CASE WHEN tblIndividuals.MailingAddress1 IS NULL THEN tblCompanies.[State] ELSE tblIndividuals.[State] END AS [State], 
CASE WHEN tblIndividuals.MailingAddress1 IS NULL THEN tblCompanies.Zip ELSE tblIndividuals.Zip END AS Zip
from tblIndividuals INNER JOIN tblCompanies
On tblIndividuals.CompanyName = tblCompanies.CompanyName
where tblIndividuals.ChristmasList=1
order by tblIndividuals.CompanyName

A few points for the future. COALESCE means "First in this list that isn't null". The CASE statement usage shouldn't be hard to figure out.

Finally, using the CompanyName as the primary key is a bad idea as the CompanyName can change presumably. Here's a good primer on database design: http://database-programmer.blogspot.com/2008/01/database-skills-third-normal-form-and.html

Upvotes: 2

Mike M.
Mike M.

Reputation: 12551

All you should need is a

select tblIndividuals.FirstName, 
..
ISNULL(tblIndividuals.MailingAddress1, tblCompanies.MailingAddress1), 
ISNULL(tblIndividuals.MailingAddress2, tblCompanies.MailingAddress2), 
...
from tblIndividuals INNER JOIN tblCompanies
On tblIndividuals.CompanyName = tblCompanies.CompanyName
where tblIndividuals.ChristmasList=1

Note that ISNULL will evaluate the first input, and if it's null, it'll display the second. MSDN HERE. You could also look at COALESCE

Upvotes: 0

Bill
Bill

Reputation: 4585

Use the CASE statement. No need for two queries since all the data is there in the first...

Select tblIndividuals.FirstName, tblIndividuals.LastName, tblIndividuals.CompanyName,
    Case 
        When tblIndividuals.MailingAddress1 IS NULL then tblCompanies.MailingAddress1 
        Else tblIndividuals.MailingAddress1 END as MailingAddress1,
    Case 
        When tblIndividuals.MailingAddress1 IS NULL then tblCompanies.MailingAddress2 
        Else tblIndividuals.MailingAddress2 END as MailingAddress2,
    ....
from tblIndividuals INNER JOIN tblCompanies
On tblIndividuals.CompanyName = tblCompanies.CompanyName
where tblIndividuals.ChristmasList=1;

Upvotes: 2

Related Questions