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.
uid,title 1, Mon 2, Tue 3, Wed etc,etc
The second table (lookup) uses the first table
uid,day_id,date,status,tech_id 1, 1,2012-01-01, 1,700 2, 2, 2012-01-02, 0,700 etc,etc
The third table (template) uses the first table
uid,day_id,status,tech_id 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
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 .....
source to share
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.)