CODEWITHSUNDEEP
mysql
Ale
Ale

Reputation: 127

MYSQL - Query to get values from one table if the value doesn't exist in another table

I'm struggling with a query. I need to create a view based on this query.

I've got 3 tables: seller, item, seller_item

Seller table

name id
S1 1
S2 2
S3 3

Item table

name id price
I1 1 50
I2 2 100

Seller_Item table

seller_id item_id price
1 1 75
2 1 25

View I'd like to obtain

nome item price
S1 I1 75
S1 I2 100
S2 I1 25
S2 I2 100
S3 I1 50
S3 I2 100

Basically, if the item is not present in the table seller_item I want to insert the default value coming from the item table otherwise the one from the seller_item table.

I was able to achieve something similar using WHERE NOT EXIST, but only when I ask for a specific seller id, instead, here, I want to have the list of all the sellers with all items.

Thank you.

----- SOLUTION -----

Thank you for your quick answers.

Just few minutes after I posted the question I found the solution. It was a lot more verbose than what I was able to find thanks your suggestions.

The final query (with all the values I need) is this one:

    SELECT
S.name AS name,
I.name AS item,
IF(SI.visible = 0, SI.visible, I.visible) AS visible,
IF(COALESCE(SI.price, 0) = 0, I.price, SI.price) AS price FROM seller S JOIN item I LEFT JOIN seller_item SI ON S.id = SI.seller_id AND I.id = SI.item_id ORDER BY 1, 2

Thank you again!

Upvotes: 0

Views: 106

Answers (3)

Shoaib
Shoaib

Reputation: 353

Hope the below query is what you are looking for

SELECT
 s.name as name,
 i.name as item,
 IFNULL(si.price, i.price) as price
FROM item i
LEFT JOIN seller_item si ON i.id = si.item_id
LEFT JOIN seller s ON s.id = si.seller_id

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You could try using a corrso join for get all the combination between item and seler and the join the seller:iem .. when the join match use the seller price otherwise use the imte price

select seller.name
    , item.name
    case when seller_item.item_id  is null then item.price else seller_item.price

from seeler 
cross join item  
left join seller_item on seller_item.item_id = item.id

Upvotes: 1

etsuhisa
etsuhisa

Reputation: 1758

You can use CROSS JOIN and COALESCE function. The query is as follows:

SELECT
    s.name nome, i.name item, COALESCE(si.price, i.price) price
FROM
    Seller s CROSS JOIN Item i
    LEFT OUTER JOIN Seller_Item si
    ON s.id=seller_id AND i.id=item_id
ORDER BY 1, 2

Fiddle

Upvotes: 1

Related Questions