MySQL Dynamically Defines Tab for Use with Internal Join

Question

He guys, I have a problem, and I hope someone can help me. I have a date. For example, 2009-10-1. This date is used to check what season I work in. It can be in summer or winter.

If the table will be used in the summer for my internal connection, then it will be "summer09_rooms". If winter is 'winter09_rooms'. Therefore, as a rule, I want to do EVENT WHEN in my INNER JOIN. How to do it. The request will 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 does not work, but now I hope you see what I want to achieve.

Respectfully,

Digital human

Answer 1

I don’t think you can join like that. Two workarounds that come to mind are using subqueries instead of internally joining or joining both two tables depending on the condition (for example, INNER JOIN summer09_rooms b ON arribal_date BETWEEN .... And a.dossier = b.dossier INNER JOIN winter09_rooms ...)

Answer 2

If you do not have matching dates in your summer and winter tables, you can combine both tables to get the desired result (I assume that 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 

This leaves the question of 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 

Thus, you get all the results from holliday, which have the appropriate dossiers in the summer, the corresponding winter.