Multiple Select Statements in SQL queries

Question

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. >.<

ristonj · Accepted Answer · 2012-05-11 17:46:44Z

2

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

answered May 11, 2012 at 17:46

ristonj

1,6081 gold badge13 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1389971 Over a year ago

Thank you so much for showing me the COALESCE statement, and the link. Very appreciated.

ristonj Over a year ago

Thanks for the accept! Can you give me an upvote, too? I'd recommend one for Bill as well as he gave essentially the same answer. Thanks again and welcome to SO.

ristonj Over a year ago

Just click on the up arrow by the number next to the answer (right now it's 0 next to mine, 2 next to Bill's).

Bill · Accepted Answer · 2012-05-11 17:35:49Z

2

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;

answered May 11, 2012 at 17:35

Bill

4,6153 gold badges24 silver badges22 bronze badges

Comments

Mike M. · Accepted Answer · 2012-05-11 17:42:34Z

0

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

answered May 11, 2012 at 17:42

Mike M.

12.6k1 gold badge26 silver badges29 bronze badges

1 Comment

ristonj Over a year ago

Sorry to vote you down, but there's a bug. Let's say that the individual has only one address line, but his company has two. Now, the address will be the first line of the individual's, but the second will be the company's. If you edit this I'll remove my downvote.

Collectives™ on Stack Overflow

Multiple Select Statements in SQL queries

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related