Anton Anton - 1 month ago 5
MySQL Question

Create view with join conditions

I would like to create a view from 2 tables with some kind of conditional join (excuse my ignorance as I am new to SQL coding!). The two tables are as follows:

TABLE1

Store | Product | MAC Price
S001 | 123 | 15.00
S001 | 456 | 17.50
S002 | 123 | 16.00
S002 | 456 | 17.50
S002 | 789 | 20.00


TABLE2:

Store | Product | SELL Price
S001 | 123 | 25.00
S001 | 456 | 27.50
S002 | 123 | 26.00
SNAT | 123 | 35.00
SNAT | 456 | 40.00


Where I am struggling with the syntax is that TABLE2 either has a price at the Store level (eg S001), or National level (eg SNAT), or no price at all.

Required View Output:

Store | Product | MAC Price | Sell Price
S001 | 123 | 15.00 | 25.00
S001 | 456 | 17.50 | 25.00
S002 | 123 | 16.00 | 26.00
S002 | 456 | 17.50 | 40.00 (no Store specifc, therefore SNAT)
S002 | 789 | 20.00 | 0.00 (no Store specifc or SNAT)


My current code looks like below... I just don't know where/how to add the rules for "if no store specific price, use SNAT, else 0.00"...

create view SCH.Z_MAC_PRICE as
select SCH.table1.store, SCH.table1.product, SCH.table1.mac,
SCH.table2.sell
from SCH.table1 left outer join
SCH.table2
on SCH.table1.store = SCH.table2.store and
SCH.table1.product = SCH.table2.product

Answer

I'd join table1 with table1 twice, once for the store level and once again for the national level, and display the national level only if the store level doesn't exist:

CREATE VIEW z_mac_price AS
SELECT      t1.store,
            t1.product,
            t1.mac_price,
            COALESCE(t2store.sell_price, t2nat.sell_pirce, 0.00)
FROM        table1 t1
LEFT JOIN   table2 t2store ON t2store.product = t1.product AND 
                              t2store.store = t1.store
LEFT JOIN   table2 t2nat ON t2nat.product = t1.product AND 
                            t2nat.store = 'SNAT'
Comments