Tommizzy Tommizzy - 1 month ago 20
MySQL Question

mySQL query with join and multiple unions

I have this query that joins two tables where a condition is met:

SELECT * FROM order
JOIN reservations
ON order.reservationsID=reservations.ID
WHERE posname='Fixed'


From this result I need to filter down to certain date ranges from the reservations table. If I were to query the reservations table (has year, month, block columns) alone I would have the following query:

(SELECT * FROM reservations
WHERE YEAR=2016 AND MONTH=11 AND BLOCK>1)
UNION
(SELECT * FROM reservations
WHERE YEAR=2016 AND MONTH>11)
UNION
(SELECT * FROM reservations
WHERE YEAR>2016)


How can I merge these two queries into one? Any guidance would be appreciated.

Answer

Try this :

SELECT * FROM [order]
JOIN reservations ON [order].reservationsID=reservations.ID
WHERE posname='Fixed' AND ( (YEAR=2016 AND MONTH=11 AND BLOCK>1) OR
                            (YEAR=2016 AND MONTH>11) OR
                            (YEAR>2016) )
Comments