#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    5
    Rep 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.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    5
    Rep Power
    0
    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.

IMN logo majestic logo threadwatch logo seochat tools logo