MySQL Dynamicly determine the tabel to use with inner join

Question

He guys, I'm stuck with a problem and I hope someone can help me out. I have a date. For example 2009-10-1. This date is used to check in which season I am working. This could be summer or winter.

If whe are in the summer the table to use for my inner join whould be 'summer09_rooms'. If winter 'winter09_rooms'. So I basicly whant to do a CASE WHEN in my INNER JOIN. How to accomplish this. The query would look like this:

SELECT name, arrival_date, departure_date FROM holliday a
INNER JOIN 
(
CASE when arrival_date BETWEEN 2009-10-1 AND 2009-4-1 THEN summer09_rooms b 
ELSE winter09_rooms b END
)
ON a.dossier=b.dossier

Of course this query isn't working but now I hope you'l see what I want to accomplish.

Kind regards,

Digital Human

Answer 1

I don't think you can do joins this way. Two workarounds that come to mind is use subselects instead of inner join or join both two tables depending on the condition (e.g. INNER JOIN summer09_rooms b ON arribal_date BETWEEN .... AND a.dossier=b.dossier INNER JOIN winter09_rooms...)

Answer 2

If you don't have overlapping dates in your summer and winter tables, you could union both tables to get the required result (I am assuming they both have the same layout).

SELECT  name
        , arrival_date
        , departure_date 
FROM    holliday a
        INNER JOIN (
          SELECT * FROM summer09_rooms
          UNION ALL SELECT * FROM winter09_rooms
        ) b ON a.dossier = b.dossier

That leaves the question though why you would have two tables in the first place.

Answer 3

SELECT 
name, 
CASE when arrival_date BETWEEN 2009-10-1 AND 2009-4-1 THEN
s.arrival_date else w.arrival_date end as arrival_date,
CASE when arrival_date BETWEEN 2009-10-1 AND 2009-4-1 THEN
s.departure_date  else w.departure_date  end as departure_date 

FROM holliday a
left JOIN summer09_rooms s on a.dossier=s.dossier and s.arrival_date BETWEEN 2009-10-1 AND 2009-4-1
left JOIN winter09_rooms w on a.dossier=w.dossier and NOT w.arrival_date BETWEEN 2009-10-1 AND 2009-4-1

This way you get all results from holliday which have corresponding dossiers in summer, respective winter.