How to join 2 different table which does not have any link

user_6873813 0
Viewed 49

How to join 2 different table which does not have any link

Table 1 - Stock on hand (has all the item of the store)

Table 2 - Sales

Now I need data from Table 1 and Table 2 in one file Table 1

ItemCode SOH_Quantity
aaaaaaaa 10
bbbbbbbb 12
cccccccc 20

Table 2

ItemCode Quantity InvoiceNo Date
aaaaaaaa 1        001       25/01/2021

I need below output

ItemCode Quantity Type
aaaaaaaa 10        I
bbbbbbbb 12        I 
cccccccc 20        I
aaaaaaaa 1         S

please help me to fetch the expected output

sql-server sql
3 Answers

What you described is not a "join", it is a "union".

Use UNION ALL.

SELECT
    ItemCode
    ,SOH_Quantity AS Quantity
    ,'I' AS Type
FROM Table1

UNION ALL

SELECT
    ItemCode
    ,Quantity
    ,'S' AS Type
FROM Table2
;
user_41160170

You can achieve that with UNION

SELECT ItemCode, SOH_Quantity as Quantity, 'I' AS TYPE
FROM table1
UNION ALL
SELECT ItemCode, Quantity, 'S' AS TYPE
FROM table2
user_91350310

You can do something like, basically it's an UNION (like an addition of rows) of two tables, not a JOIN:

    SELECT ItemCode, SOH_Quantity AS Quantity, 'I' as Type FROM Table1
    UNION ALL
    SELECT ItemCode, Quantity, 'S' AS Type FROM Table2

Note how we made sure that we fetch the same column names from both tables for this to work.

user_4792510
Related