CODEWITHSUNDEEP
sqlmariadb
Jonathan Dewein
Jonathan Dewein

Reputation: 984

How to conditionally join two tables

I have two tables which I need to join depending upon their values.

TABLE coursemat
+-----+--------+----------+
| txt | price  | material |
+-----+--------+----------+

Table coprices
+--------+----------+
| price  | material |
+--------+----------+

They are connected by the material key.

If I search coursemat.material and find that coprices.material is equal, then I must use coprices.price instead of the coursemat.price.

This is what I have so far:

SELECT coursemat.txt, coursemat.price, coursemat.material, coprices, country
FROM coursemat
JOIN corprices ON coursemat.material = coprices.material;

But this isn't quite getting what I want.

Essentially, I want to use coursemat.price if coprices.price does not exist for the same material and coprices.material does exist, then I want to use coprices.price instead of coursemat.price.

Upvotes: 0

Views: 62

Answers (2)

SBFrancies
SBFrancies

Reputation: 4240

If I understand what you want correctly you can use a left join and the IFNULL statement:

SELECT
    coursemat.txt, 
    IFNULL(coprices.price, coursemat.price),
    coursemat.material, 
    coprices.country
FROM
    coursemat
LEFT JOIN
    corprices 
    ON coursemat.material = coprices.material;

Upvotes: 3

kjmerf
kjmerf

Reputation: 4345

Another option is COALESCE:

SELECT cm.txt, cm.material, COALESCE(cp.price, cm.price) AS price
FROM coursemat cm
LEFT JOIN corprices cp ON cm.material = cp.material;

Finally, you could also use a CASE statement:

SELECT cm.txt, cm.material, 
CASE WHEN cp.price IS NOT NULL THEN cp.price ELSE cm.price END AS price
FROM coursemat cm
LEFT JOIN corprices cp ON cm.material = cp.material;

Upvotes: 1

Related Questions