Return columns from either table B or C based on a match in Table A

Dev Shed Forums Sponsor:

July 26th, 2012, 02:26 PM

Night1505

Registered User

Join Date: Jul 2012

Posts: 5

Time spent in forums: 2 h 52 m
Reputation Power: 0

Return columns from either table B or C based on a match in Table A

This is stumping me terrribly, so I will try to break it down as best I can.

I'm trying to return all columns from Table A, and either certain columns from TableB or certain columns from TableC, depending on which one of the latter two tables contains a match with a column in TableA.

More specifically, I have TableA which contains a column called "LocationID". TableB contains a column called "SiteID". TableC contains a column called "DealerCode".

If TableA.LocationID = TableB.SiteID, then I need to return columns from TableB, but if TableA.LocationID = TableC.DealerCode, then I need to return columns from TableC.

Here is an example of some SQL I wrote for SQL Server 2005; it is syntactically correct, but times out when I'm running it. Also, when I actually do manage to tweak it so that it will run, I get tons of duplicates from TableA. I tried using a DISTINCT, but that made it time out again. Here is an example of what I'm trying to do:

SELECT

TA.Column1, TA.Column2, TA.Column3, TA.Column4, TA.Column5, TA.Column6, TA.Column7, TA.Column9, TA.Column10, TA.Column11, TA.Column12, TA.Column13, TA.Column14, TA.Column15, TA.Column16, TA.Column17, TA.Column18, TA.LocationId, (CASE WHEN TA.LocationID IN (SELECT SiteID FROM TableB) THEN TB.SiteID WHEN TA.LocationID IN (SELECT DealerCode FROM TableC) THEN TC.DealerCode END) As SiteID, (CASE WHEN TA.LocationID IN (SELECT SiteID FROM TableB) THEN TB.Column3 WHEN TA.LocationID IN (SELECT DealerCode FROM TableC) THEN TC.Column3 END) As LocationName, (CASE WHEN TA.LocationID IN (SELECT SiteID FROM TableB) THEN TB.Column4 WHEN TA.LocationID IN (SELECT DealerCode FROM TableC) THEN TC.Column4 END) As Address1, (CASE WHEN TA.LocationID IN (SELECT SiteID FROM TableB) THEN TB.Column5 WHEN TA.LocationID IN (SELECT DealerCode FROM TableC) THEN TC.Column5 END) As City

FROM

dbo.TableA AS TA LEFT OUTER JOIN
dbo.TableB AS TB ON TB.SiteID = TA.LocationId LEFT OUTER JOIN
dbo.TableC AS TC ON TC.DealerCode = TA.LocationId

ORDER BY TA.Column2

Any help would be greatly appreciated.

Thanks in advance!

--Chris D.

July 27th, 2012, 05:29 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,375

Time spent in forums: 3 Months 1 Week 2 Days 7 h 30 m 16 sec
Reputation Power: 4140

Code:

SELECT TA.Column1
     , TA.Column2
     , TA.Column3
     , TA.Column4
     , TA.Column5
     , TA.Column6
     , TA.Column7
     , TA.Column9
     , TA.Column10
     , TA.Column11
     , TA.Column12
     , TA.Column13
     , TA.Column14
     , TA.Column15
     , TA.Column16
     , TA.Column17
     , TA.Column18
     , TA.LocationId
     , CASE WHEN TA.LocationID = TB.SiteID     THEN TB.SiteID  
            WHEN TA.LocationID = TC.DealerCode THEN TC.DealerCode END  AS SiteID
     , CASE WHEN TA.LocationID = TB.SiteID     THEN TB.Column3 
            WHEN TA.LocationID = TC.DealerCode THEN TC.Column3    END  AS LocationName
     , CASE WHEN TA.LocationID = TB.SiteID     THEN TB.Column4 
            WHEN TA.LocationID = TC.DealerCode THEN TC.Column4    END  AS Address1
     , CASE WHEN TA.LocationID = TB.SiteID     THEN TB.Column5 
            WHEN TA.LocationID = TC.DealerCode THEN TC.Column5    END  AS City
  FROM dbo.TableA AS TA 
LEFT OUTER 
  JOIN dbo.TableB AS TB 
    ON TB.SiteID = TA.LocationId 
LEFT OUTER 
  JOIN dbo.TableC AS TC 
    ON TC.DealerCode = TA.LocationId
ORDER 
    BY TA.Column2

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

July 29th, 2012, 10:49 AM

Night1505

Registered User

Join Date: Jul 2012

Posts: 5

Time spent in forums: 2 h 52 m
Reputation Power: 0

Quote:

Originally Posted by r937

Code:

SELECT TA.Column1
     , TA.Column2
     , TA.Column3
     , TA.Column4
     , TA.Column5
     , TA.Column6
     , TA.Column7
     , TA.Column9
     , TA.Column10
     , TA.Column11
     , TA.Column12
     , TA.Column13
     , TA.Column14
     , TA.Column15
     , TA.Column16
     , TA.Column17
     , TA.Column18
     , TA.LocationId
     , CASE WHEN TA.LocationID = TB.SiteID     THEN TB.SiteID  
            WHEN TA.LocationID = TC.DealerCode THEN TC.DealerCode END  AS SiteID
     , CASE WHEN TA.LocationID = TB.SiteID     THEN TB.Column3 
            WHEN TA.LocationID = TC.DealerCode THEN TC.Column3    END  AS LocationName
     , CASE WHEN TA.LocationID = TB.SiteID     THEN TB.Column4 
            WHEN TA.LocationID = TC.DealerCode THEN TC.Column4    END  AS Address1
     , CASE WHEN TA.LocationID = TB.SiteID     THEN TB.Column5 
            WHEN TA.LocationID = TC.DealerCode THEN TC.Column5    END  AS City
  FROM dbo.TableA AS TA 
LEFT OUTER 
  JOIN dbo.TableB AS TB 
    ON TB.SiteID = TA.LocationId 
LEFT OUTER 
  JOIN dbo.TableC AS TC 
    ON TC.DealerCode = TA.LocationId
ORDER 
    BY TA.Column2

I can't believe that all I needed to do was change the IN LIST SELECT statements to equality equations.

This works perfectly for what I need it for. Thanks very much.

Return columns from either table B or C based on a match in Table A

Developer Shed Advertisers and Affiliates