CODEWITHSUNDEEP
sqlsql-server
Jackie
Jackie

Reputation: 494

SQL Server : how to cross join table

I have SQL Server tables as follows and need to perform join to produce results as below.

Custorder: 

itemcode   orderqty
-------------------
item001      10
item003      30
item004      50

manufactured: 

itemcode    manuqty
--------------------
item001       100
item002       200
item004       300

How can I join these two tables to produce 

itemcode    orderqty    manuqty  
---------------------------------
item001        10         100
item002       NULL        200
item003        30         NULL
item004        50         300

Appreciate any help.

Upvotes: 0

Views: 68

Answers (2)

JamieD77
JamieD77

Reputation: 13949

you can use full outer join for this.

SELECT  ISNULL(co.itemcode, ma.itemcode) itemcode ,
        orderqty ,
        manuqty
FROM    custorder co
        FULL OUTER JOIN manufactured ma ON co.itemcode = ma.itemcode

another option you have is to use a query to build your itemcode list from both tables and join to it to get your result.. this could result in a very slow query if you have a lot of records.. Ideally you would have a table similar to inventory (itemid int, itemcode varchar) and you would use that table to outer join to the others..

;WITH items AS (
    --get distinct itemcodes from both tables
    SELECT itemcode FROM custorder
    UNION
    SELECT itemcode FROM manufactured  
)
SELECT  i.itemcode,
        co.orderqty,
        ma.manuqty
FROM    custorder co
        RIGHT OUTER JOIN items i ON i.itemcode = co.itemcode
        LEFT OUTER JOIN manufactured ma ON i.itemcode = ma.itemcode

Upvotes: 4

Charles Bretana
Charles Bretana

Reputation: 146607

First, the phrase "Cross-Join" is used in DB community to refer to a join that produces what is called a "Cartesian product". For e.g., the alphabet cross-joined with itself would produce AA, AB, AC, AD...AZ, BA, BB, BC, etc.

What you want is just a regular join:

 Select coalesce(co.itemcode, m.itemcode) itemCode, 
    co.orderqty, m.manuqty
 From custorder co
    full join manufactured m 
        on m.itemcode = co.itemcode

Upvotes: 1

Related Questions