CODEWITHSUNDEEP
sqldatabase
Jay12
Jay12

Reputation: 29

Getting Data from a Table, if it exists on another table get it from there

lets say I have 2 tables(lets call it Price and SP(for special price)). I want to get all rows from table Price, but if it matches something from Table SP(meaning a special price exists for that row), ill apply those values instead. Ive been testing some joins for a while now and Im not sure if it is the correct approach, please advise, thanks!

Upvotes: 0

Views: 35

Answers (2)

Mark Miller
Mark Miller

Reputation: 7447

This seems to work for me with mysql:

SELECT
IF(ISNULL(sp.price) OR sp.price='', p.price, sp.price) AS price
FROM price p
JOIN specialprice sp ON sp.id = p.id

UPDATE

Rethinking this, this will only work if there is a matching row in specialprice for every row in price - just with an empty field if there is no special price value.

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

As there can be an entry in SP but doesn't have to, you need to outer join the table. Then check if you got a value or not. COALESCE does that for you.

select
  price.item,
  coalesce(sp.value, price.value)
from price
left join sp on sp.item = price.item;

Upvotes: 1

Related Questions