CODEWITHSUNDEEP
mysqlsql
JK87
JK87

Reputation: 379

MySQL: Use value from table 2 instead of table 1 when exists in table 2

I have a transport planner written in PHP and MySQL, To get the task rules per day, I use the following query:

SELECT  planning.*,     
        planning_dagen.planning_id, 
        planning_dagen.dagen,
        planning_dagen.data_import,
        routenummer_wijzigingen.routenummer AS temp_routenummer
FROM planning 
LEFT JOIN planning_dagen
USING (planning_id)
LEFT JOIN routenummer_wijzigingen
USING (planning_id)
WHERE :datum >= planning.datum
AND :datum <= geldig_tot
AND (frequentie = 'dagelijks' AND dayofweek(:datum) = planning_dagen.dagen
OR (frequentie = 'eenmalig' AND date(:datum) = planning.datum)
OR (frequentie = 'wekelijks' AND 0 = (abs(datediff(:datum, planning.datum)) % 7))
OR (frequentie = 'twee-wekelijks' AND 0 = (abs(datediff(:datum, planning.datum)) % 14))
OR (frequentie = 'maandelijks'
AND ceil(dayofmonth(:datum)/7) = ceil(dayofmonth(planning.datum)/7)
AND dayofweek(:datum) = dayofweek(planning.datum)))
AND dayofweek(:datum) <> '1'
AND dayofweek(:datum) <> '7'

In the planning table there is a column called routenummer (routenumber) which is used in most conditions (standard routenumber). But as you can see I have also a routenummer_wijzigingen table which is used to give a task a different routenumber for certain day.

For example I have a task which returns every tuesday and wednesday and has routenumber 10. But on tuesday 2015-02-03 I need this task done by routenumber 9. So I insert a rule in the routenummer_wijzigingen table which has the following columns:

routenummer_wijzigingen_id
planning_id
routenummer
datum

So when a date is selected and that date and planning_id exists in the routenummer_wijzigingen table, it has to take the routenumber from the routenummer_wijzigingen table instead of the planning table. How can I achieve this?

Upvotes: 2

Views: 44

Answers (1)

rtruszk
rtruszk

Reputation: 3922

You should modify join condition with routenummer_wijzigingen table (including datum). Then you should use CASE in your SELECT clause to decide which routenummer to choose.

SELECT  planning.*,     
    planning_dagen.planning_id, 
    planning_dagen.dagen,
    planning_dagen.data_import,
    CASE
        WHEN routenummer_wijzigingen.routenummer is not NULL
            THEN routenummer_wijzigingen.routenummer
        ELSE planning.routenummer
    END AS temp_routenummer
FROM planning
...
LEFT JOIN routenummer_wijzigingen rw on 
    planning.planning_id=rw.planning_id and rw.datum=...

Upvotes: 1

Related Questions