How to use another table in if (like) conditon in MySQL

I have three tables The first table (day) just contains the values, i.e.

1, Mon
2, Tue
3, Wed


The second table (lookup) uses the first table

1, 1,2012-01-01, 1,700
2, 2, 2012-01-02, 0,700


The third table (template) uses the first table

1, 1, 1,700
2, 2, 0,700
3, 3, 0,700


In fact, each table contains a little more information that is shown, but this data is not current at this time.

What I'm trying to do is doing all the steps in a single SQL function and not relying on code. I am trying to get a SQL function to use the templates table instead of the lookup table if there is no record that day

In psudocode:

column 1 = * from days
column 2 = status from(if (template != null, use template)elseif(lookup != null, use lookup)else(null))
where tech_id=700


Is this possible in MySQL? If so, how? I tried to do this with INNER / LEFT (outer) joins and subqueries, but so far to no avail :(


source to share

3 answers

I think you want to use the COALESCE function:

SELECT d.*, COALESCE(SELECT status FROM lookup WHERE day_id =, SELECT status FROM template WHERE day_id = as status
FROM day AS d
WHERE etc, etc




After playing around a bit, I realized it was a bit immodest, but it works.

Since this is done by PHP there can be variables, replace $ tech_id with 700

SELECT da.*,
        (SELECT status FROM lookup AS lo WHERE tech_id=$tech_id  AND da.uid = lo.day_id) as lookup,
        (SELECT status FROM template AS te WHERE tech_id=$tech_id AND da.uid = te.day_id) as template
        FROM day AS da


It does it in two separate columns, but that's enough for now .....



I believe it will work. It uses a function COALESCE


SELECT day.uid, day.title, COALESCE(lo.status, te.status) AS status
FROM day
LEFT JOIN lookup lo ON lo.day_id = day.uid
LEFT JOIN template te ON te.day_id = day.uid
WHERE lo.tech_id = $tech_id
   OR te.tech_id = $tech_id


Basically this creates a large flat set and then fetches the first non-null column from lo.status

or te.satus

for each record. LEFT JOIN

means that added columns (for example, status

or tech_id

) can be NULL

if there are no records in the corresponding tables that were merged into day.uid

. Please note that use OR

as tech_id

anyway can also be NULL

at this point. (The query above works in premsie, which (day_id,tech_id)

is a candidate key for lookup tables and templates.)



All Articles