SQL Query - 3 Tables - Table 1 joins to table 2, table 2 joins to table 3, all to get id from table-CodePudding

I have a question which is somewhat difficult to put into words, though I hope using a desired output will help to clarify what I am asking.

I have three tables. First table contains CustomerIDs and names:

CREATE TABLE #CustomerTable1 
(
    CustomerID VARCHAR(25), 
    CustomerName VARCHAR(25)
) 

INSERT INTO #CustomerTable1 
VALUES ('0156', 'Frank'), ('0178', 'Darrull'), 
       ('0908', 'Mary'), ('0785', 'Bertha')

CustomerID  CustomerName
-------------------------
0156        Frank
0178        Darrull
0908        Mary
0785        Bertha

Second table contains the CustomerID from table 1, a LineNBR column, and what I call an ArbitraryID. A customer can only have one ArbitraryID, but several LineNBRs:

CREATE TABLE #CustomerTable2 
(
    CustomerIDFromTable1 varchar(25), 
    LineNBR VARCHAR(25), 
    ArbitraryID VARCHAR(25)
)

INSERT INTO #CustomerTable2 
VALUES ('0156', '1', '167483'), ('0156', '2', NULL), 
       ('0156', '3', NULL), ('0156', '4', NULL),
       ('0178', '1', NULL), ('0178', '2', '873923'), 
       ('0178', '3', NULL), ('0178', '4', NULL),
       ('0908', '1', NULL), ('0908', '2', NULL), 
       ('0908', '2', NULL), ('0908', '4', NULL),
       ('0785', '1', NULL), ('0785', '2', NULL),
       ('0785', '3', NULL), ('0785', '4', NULL)


CustomerIDFromTable1    LineNBR ArbitraryID
-------------------------------------------
0156                    1       167483
0156                    2       NULL
0156                    3       NULL
0156                    4       NULL
0178                    1       NULL
0178                    2       873923
0178                    3       NULL
0178                    4       NULL
0908                    1       NULL
0908                    2       NULL
0908                    3       NULL
0908                    4       NULL
0785                    1       NULL
0785                    2       NULL
0785                    3       NULL
0785                    4       NULL

Third table contains any ArbitraryID from table 2 and an additional ID that I label OtherID. There can only be one OtherID per ArbitraryID:

CREATE TABLE #CustomerTable3 
(
    ArbitraryIDFromTable2 VARCHAR(25), 
    OtherID VARCHAR(25)
)

INSERT INTO #CustomerTable3 
VALUES ('167483', '89987648'), ('873923', '45564783')

ArbitraryIDFromTable2   OtherID
---------------------------------
167483                  89987648
873923                  45564783

My question is this:

How do I join these three tables to make sure to get the OtherID, where it exists, for the CustomerIDs that have an a non-null ArbitraryID in table 2?

The results should look like this:

CustomerID      CustomerName    OtherIDFromTable3
--------------------------------------------------
0156            Frank           89987648
0178            Darrull         873923
0908            Mary            NULL
0785            Bertha          NULL

I've started with this, but am getting duplicates, of course:

SELECT DISTINCT
    a.CustomerID AS CustomerIDFinal,
    a.CustomerName,
    c.OtherID
FROM
    #CustomerTable1 a
LEFT OUTER JOIN 
    #CustomerTable2 b ON a.CustomerID = b.CustomerIDFromTable1
LEFT OUTER JOIN 
    #CustomerTable3 c ON c.ArbitraryIDFromTable2 = b.ArbitraryID

EDIT! Just out of curiosity, wondering if someone can solve with an updated version of #CustomerTable2

CREATE TABLE #CustomerTable2 
( CustomerIDFromTable1 varchar(25), 
  LineNBR VARCHAR(25), 
  ArbitraryID VARCHAR(25))

INSERT INTO #CustomerTable2 VALUES
('0156', '1', '167483'), ('0156', '2', '167483'), 
('0156', '3', '167483'), ('0156', '4', '167483'),
('0178', '1', '873923'), ('0178', '2', '873923'), 
('0178', '3', '873923'), ('0178', '4', NULL),
('0908', '1', NULL), ('0908', '2', NULL), 
('0908', '2', NULL), ('0908', '4', NULL),
('0785', '1', NULL), ('0785', '2', NULL), 
('0785', '3', NULL), ('0785', '4', NULL)

CodePudding user response：

If you don't actually need the join (and just want the column from table 3), one way of doing it would be to just put a subquery for that column:

SELECT
    a.CustomerID AS CustomerIDFinal
    ,a.CustomerName
    ,(SELECT c.OtherId FROM #CustomerTable3 c INNER JOIN #CustomerTable2 b ON c.ArbitraryIDFromTable2 = b.ArbitraryID WHERE a.CustomerID = b.CustomerIDFromTable1) OtherId
FROM #CustomerTable1 a

With a join, you probably want something like:

SELECT
    a.CustomerID AS CustomerIDFinal
    ,a.CustomerName
    ,d.OtherID
FROM #CustomerTable1 a
 LEFT OUTER JOIN 
 (
  SELECT c.OtherId, b.CustomerIDFromTable1 
  FROM #CustomerTable3 c INNER JOIN #CustomerTable2 b
   ON c.ArbitraryIDFromTable2 = b.ArbitraryID
 ) d
  ON a.CustomerID = d.CustomerIDFromTable1

This is the same idea. You use an inner join between table 2 and 3 (eliminates the "duplicates" you see, but use a left join with table 1 because not every record in table 1 matches a record in table 3.

Here is a fiddle of it giving your desired output.

Added for the updated question: You don't say which value you want if there are multiple matches, but if you don't care, you care use MAX like:

SELECT
    a.CustomerID AS CustomerIDFinal
    ,a.CustomerName
    ,(SELECT max(c.OtherId) FROM #CustomerTable3 c INNER JOIN
    #CustomerTable2 b ON c.ArbitraryIDFromTable2 = b.ArbitraryID WHERE a.CustomerID = b.CustomerIDFromTable1) OtherId
FROM #CustomerTable1 a

If the particular row you care about matters, you can use an aggregate function other than max or you can incorporate a row_number() OVER (ORDER BY line_nr) clause (or something of that nature).

CodePudding user response：

When you create table second and third table, you should include FOREIGN KEY, so they can connect. Try alter your table and add FOREIGN KEY.

ALTER TABLE Orders
    ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

https://www.w3schools.com/sql/sql_foreignkey.asp

CodePudding user response：

You are getting a Cartesian result/product as a result of your line number table (Table2) where there are more entries per customer. Basically, what you need to do is a LEFT JOIN to table2 by ID AND NOT NULL of the Abritrary ID, then left-join continues to the third table.

select DISTINCT
      t1.CustomerID,
      t1.CustomerName,
      t3.OtherID
   from
      #CustomerTable1 t1
         LEFT JOIN #CustomerTable2 t2
            on t1.CustomerID = t2.CustomerIDFromTable1
            AND NOT t2.ArbitraryID IS NULL
            LEFT JOIN #CustomerTable3 t3
               on t2.ArbitraryID = t3.ArbitraryIDFromTable2

Since your rule indicates that table 2 can only have ONE instance of an ArbitraryID per customer, adding the

AND NOT t2.ArbitraryID IS NULL

means I only want to see those that DO have the Arbitrary ID. Which is then finally joined to table 3 for its final OtherID value

REVISION based on comment. I was adding the line number even though it was not explicitly asked for just to show how the context of which record qualified the join.

To qualify similar results, you changed your rule that the second table would only have ONE entry with an arbitrary ID, but your NEW sample data has multiple rows for a single customer having the SAME abitrary ID. TO handle, I removed the LineNBR column and just added DISTINCT. Distinct prevents duplicates of whatever returned columns, thus ignoring the extra lines per customer that had the arbitrary ID assigned.