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

Question

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!

etsuhisa · Accepted Answer · 2021-02-26 11:43:53Z

1

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

answered Feb 26, 2021 at 11:43

etsuhisa

1,7881 gold badge7 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ScaisEdge · Accepted Answer · 2021-02-26 11:44:53Z

1

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

answered Feb 26, 2021 at 11:44

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Comments

Shoaib · Accepted Answer · 2021-02-26 11:51:38Z

1

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

edited Feb 26, 2021 at 11:51

answered Feb 26, 2021 at 11:45

Shoaib

3532 silver badges10 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related