Geek asks and answers

Multiple select queries in SQL queries

I recognize myself as an absolute SQL Noob here (I can do internal joins, but it is about as complicated as my knowledge with SQL), but I hope for some help / understanding / ideas on how to best achieve something, I have a table of companies and a subheading of individuals for these companies. A company may have a mailing address, but it is possible that a person will have a different mailing address in order to redefine the usual company address. I have SQL Server 2008 in my environment. My thought was for the SQL query loop to execute twice as follows.

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

, , , , , , , . , SQL, , , , . - SQL, , ? . .

edit: , , / .. > & ;

+3
3
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

. COALESCE " , ". CASE .

, CompanyName - , _ . : http://database-programmer.blogspot.com/2008/01/database-skills-third-normal-form-and.html

+2

CASE. , ...

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;
+2

, ,

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

, ISNULL , , . MSDN . COALESCE

0

More articles:


All Articles