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
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
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
for each record.
means that added columns (for example,
) can be
if there are no records in the corresponding tables that were merged into
. Please note that use
anyway can also be
at this point. (The query above works in premsie, which
is a candidate key for lookup tables and templates.)