CODEWITHSUNDEEP
sqlsql-server-2008t-sql
notlkk
notlkk

Reputation: 1231

SQL Self Left Join Challenge

I have a table like this:

ManufacturerID   ProductID     Price    Region
==============================================
100              1             12.00    A
100              2             20.00    A
100              3             25.00    A
100              4             30.00    B
101              1             15.00    A
101              2             20.00    A
101              4             30.00    B

I want to get a query result that compares 2 different manufacturers to look like this:

ProductID     Price1    Price2    Region
=========================================================================
1             12.00     15.00     A
2             20.00     20.00     A
3             25.00     null      A
4             30.00     30.00     B

I try to use left join on the same table:

SELECT ProductID, a.Price AS Price1, b.Price AS Price2, a.Region
FROM   table1 a 
       LEFT JOIN table1 b ON a.ProductID = b.ProductID AND a.ManufacturerID = 100
WHERE  b.ManufacturerID = 101

but this doesn't give me the missing product (ID:4) from Manufacturer 101. What am I missing?

Upvotes: 1

Views: 322

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

Since you can't know in advance which product will be missing, for example manufacturer A might be missing product 3 and manufacture B missing product 8, you need a FULL OUTER join, if you want to do this with a join (Gordon provided a different way to go).

I assumed that the (ManufacturerID ,ProductID, Region) combination has a UNIQUE constraint:

SELECT COALESCE(a.ProductID, b.ProductID) AS ProductID, 
       a.Price AS Price1, 
       b.Price AS Price2, 
       COALESCE(a.Region, b.Region) AS Region
FROM   
       ( SELECT ProductID, Price, Region
         FROM table1
         WHERE ManufacturerID = 100
       ) AS a
    FULL JOIN 
       ( SELECT ProductID, Price, Region
         FROM table1
         WHERE ManufacturerID = 101
       ) AS b
           ON  a.ProductID = b.ProductID 
           AND a.Region = b.Region      -- not sure if you need this line
;

Tested at SQL-Fiddle (thnx @Thomas)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270653

I would do this using aggregation, rather than a join:

select ProductId,
       MAX(case when ManufacturerId = 100 then Price end) as Price1,
       MAX(case when ManufacturerId = 101 then Price end) as Price2,
       Region
from table1
where ManufacturerId in (100, 101)
group by ProductId, Region;

Upvotes: 2

user359040
user359040

Reputation:

You have a.ManufacturerID and b.ManufacturerID the wrong way round in the on and where clauses - try:

SELECT ProductID, a.Price AS Price1, b.Price AS Price2, a.Region
FROM   table1 a 
       LEFT JOIN table1 b ON a.ProductID = b.ProductID AND b.ManufacturerID = 101
WHERE  a.ManufacturerID = 100

Upvotes: 3

Related Questions