CODEWITHSUNDEEP
sql-servert-sqljointable-variable
Harsha W
Harsha W

Reputation: 3366

Join 2 Table Variables in SQL

How to join 2 table variables which are not consists of a foreign key column.

DECLARE @InventoryIDList TABLE(ID INT)
DECLARE @ProductSupplierIDList TABLE(ID INT)

Excepted output

@InventoryList
--------------
123
456
789
111

@ProductSupplierIDList
--------------
999
888
777
666

@InventoryList    ProductSupplierIDList
---------------------------------------
123             |        999
567             |        888
789             |        777
111             |        666

All are random data. I just want to combine the 2 table variable to look like above. I tried all the types of joins. But I need to have the upper mentioned output without having null values.

I tried the CROSS APPLY 

SELECT *
FROM @InventoryIDList invList CROSS APPLY @ProductSupplierIDList prdList

But it gives me 5^2 number of elements as the result with duplicates.

Upvotes: 3

Views: 5753

Answers (3)

dfundako
dfundako

Reputation: 8324

Since the IDs are not in sequential order and can be random, I would recommend using an Identity on the table variables and joining on that:

DECLARE @InventoryIDList TABLE(JoiningID INT IDENTITY(1,1), ID INT)
DECLARE @ProductSupplierIDList TABLE(JoiningID INT IDENTITY(1,1), ID INT)

INSERT INTO @InventoryIDList 
VALUES 
(123),
(456),
(789),
(111)

INSERT INTO @productsupplierIDList 
VALUES 
(999),
(888),
(777),
(666)


SELECT i.id, p.id
FROM @inventoryIDList i
INNER JOIN @productsupplierIDList p
    oN i.joiningid = p.JoiningID

Upvotes: 3

Thom A
Thom A

Reputation: 95949

Assuming that the JOIN criteria is the same "row number" in ascending ID order:

WITH invList AS (
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY ID) AS RN
    FROM @InventoryIDList),
prdList AS (
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY ID) AS RN
    FROM @ProductSupplierIDList)
SELECT *
FROM invList IL
     JOIN prdList PL ON IL.RN = PL.RN;

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93754

I guess you need Row_Number and Full Outer Join, considering there is no relation between those 2 tables 

SELECT I.ID,
       P.ID
FROM   (SELECT Rn = Row_number()OVER(ORDER BY ID),*
        FROM   @InventoryList) I
       FULL JOIN (SELECT Rn = Row_number()OVER(ORDER BY ID),*
                  FROM   @ProductSupplierIDList) p
              ON I.RN = P.RN

Upvotes: 3

Related Questions