CODEWITHSUNDEEP
mysqlsqlselectunion-all
djmzfKnm
djmzfKnm

Reputation: 27195

Union all, combining query results

I have following MySQL queries:

SELECT * 
FROM bookings 
WHERE record_id = 7 AND status = 'available' AND startdate >= '2015-05-02' AND startdate <= '2015-05-09'
UNION ALL
SELECT * 
FROM bookings 
WHERE record_id = 7 AND status = 'available' AND startdate >= '2015-05-11' AND startdate <= '2015-05-12'

Is it possible to combine these two queries, instead of using UNION ALL ?

Upvotes: 3

Views: 59

Answers (4)

Robert
Robert

Reputation: 25763

You can use OR operator instead as below 

SELECT * 
FROM bookings 
WHERE record_id = 7 
AND status = 'available' 
AND ((startdate >= '2015-05-11' AND startdate <= '2015-05-12') or (startdate >= '2015-05-02' AND startdate <= '2015-05-09'))

Upvotes: 2

Jeremy C.
Jeremy C.

Reputation: 2465

Leave this part: 

SELECT * FROM bookings WHERE record_id = 7 AND status = 'available'

Add this:

AND ((startdate >= '2015-05-02' AND startdate <= '2015-05-09') OR (startdate >= '2015-05-11' AND startdate <= '2015-05-12'))

OR condition will return true if you get a true for the first and condition Or the second and condition

Also for those and conditions you are using you might want to take a look at the sql BETWEEN statement : http://www.w3schools.com/sql/sql_between.asp

You could also do this after your first part:

AND startDate >= '2015-05-02' AND startDate <= '2015-05-12' AND NOT startDate = '2015-05-10'

As you are using hard coded dates anyways and the only one in that range you don't need is the 10th of May

Upvotes: 1

Mureinik
Mureinik

Reputation: 312309

You could just use an OR operator to get both date ranges:

SELECT * 
FROM   bookings 
WHERE  record_id = 7 AND
       status = 'available' AND 
       ((startdate >= '2015-05-02' AND startdate <= '2015-05-09') OR
        (startdate >= '2015-05-11' AND startdate <= '2015-05-12'))

Upvotes: 2

Jens
Jens

Reputation: 69505

This schould work:

SELECT * 
FROM bookings 
WHERE record_id = 7 AND status = 'available' AND 
   ((startdate >= '2015-05-02' AND startdate <= '2015-05-09') or (startdate >= '2015-05-11' AND startdate <= '2015-05-12'))

Upvotes: 2

Related Questions