MYSQL filter on multiple dates as array

Question

The code below works like it should, but now I want to add multiple dates like this:

reservationStartDate = 2018-07-16, 2018-07-17

SELECT 
    *,
    SUM(IF(reservationId IS NULL,
        0,
        reservationStartDate = '2018-07-17')) AS ConflictingReservations
FROM
    units
        LEFT JOIN
    reservations ON (unitsId = reservationSpace)
WHERE
    unitsType = 'room1'
GROUP BY unitsId
HAVING ConflictingReservations = 0;

Use IN clause.

PM 77-1
– PM 77-1

2018-07-02 15:18:32 +00:00
Commented Jul 2, 2018 at 15:18 — PM 77-1
– PM 77-1, Commented Jul 2, 2018 at 15:18
@PM77-1 In an IF statement?

AbraCadaver
– AbraCadaver

2018-07-02 15:19:20 +00:00
Commented Jul 2, 2018 at 15:19 — AbraCadaver
– AbraCadaver, Commented Jul 2, 2018 at 15:19

Gordon Linoff · Accepted Answer · 2018-07-02 15:29:16Z

2

I would just do:

SELECT u.unit_id,
       SUM(r.reservationStartDate IN ('2018-07-17')) AS ConflictingReservations
FROM units u LEFT JOIN
     reservations r
     ON u.unitsId = r.reservationSpace
WHERE u.unitsType = 'room1'
GROUP BY u.unitsId
HAVING ConflictingReservations = 0;

Or, even more simply:

SELECT u.*
FROM units u LEFT JOIN
     reservations r
     ON u.unitsId = r.reservationSpace AND
        r.reservationStartDate in ( . . . )
WHERE u.unitsType = 'room1' AND r.reservationSpace IS NULL;

No GROUP BY is needed at all, because you are not using the count.

answered Jul 2, 2018 at 15:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Roshana Pitigala · Accepted Answer · 2018-07-02 15:20:30Z

1

Use OR

(reservationStartDate = '2018-07-16' OR reservationStartDate = '2018-07-17')

The complete query would be

SELECT 
    *,
    SUM(IF(reservationId IS NULL,
        0,
        (reservationStartDate = '2018-07-17'
            OR reservationStartDate = '2018-07-17'))) AS ConflictingReservations
FROM
    units
        LEFT JOIN
    reservations ON (unitsId = reservationSpace)
WHERE
    unitsType = 'room1'
GROUP BY unitsId
HAVING ConflictingReservations = 0;

answered Jul 2, 2018 at 15:20

Roshana Pitigala

8,8569 gold badges53 silver badges87 bronze badges

Collectives™ on Stack Overflow

MYSQL filter on multiple dates as array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related