CODEWITHSUNDEEP
mysql
pufAmuf
pufAmuf

Reputation: 7805

Retrieve Venues where there are No Events on that day Only

I'm trying to retrieve rows from a venues table where there are no events placed on that day.

This is how I would retrieve an event:

SELECT
    events.*,
    venues.*,
    party_types.PARTYTYPE
FROM events
INNER JOIN venues 
    ON events.VENUE_LOCATION = venues.ID
WHERE start_datetime >= '$DATE_START_SELECTED'
AND end_datetime < '$DATE_END_SELECTED' 
GROUP BY events.ID

Where $DATE_START_SELECTED and $DATE_END_SELECTED are PHP variables that basically say Take all events between Date1 and Date2.

This is how I currently retrieve venues:

SELECT
venues.*
FROM venues
GROUP BY venues.VENUE_NAME

How would I go about retrieving all venues where there are no events held on that day (dependent on the PHP variables)?

Thanks everyone! :)

Upvotes: 0

Views: 36

Answers (3)

Euclides Mul&#233;mbw&#232;
Euclides Mul&#233;mbw&#232;

Reputation: 1687

try this, I believe it won't perform very well, but it will give the correct answer.

SELECT
venues.*
FROM venues
WHERE ID NOT IN (SELECT
    VENUE_LOCATION
    FROM events
    WHERE start_datetime >= '$DATE_END_SELECTED'
    OR end_datetime < '$DATE_START_SELECTED')
GROUP BY venues.VENUE_NAME

Upvotes: 1

Mircea Soaica
Mircea Soaica

Reputation: 2817

SELECT
venues.*
FROM venues
LEFT JOIN events ON events.VENUE_LOCATION = venues.ID  AND start_datetime >= '$DATE_START_SELECTED' AND end_datetime < '$DATE_END_SELECTED'
where events.VENUE_LOCATION IS NULL
GROUP BY venues.VENUE_NAME

Upvotes: 2

robertvoliva
robertvoliva

Reputation: 902

SELECT
venues.*,
FROM venues 
LEFT JOIN events
ON events.VENUE_LOCATION = venues.ID
WHERE start_datetime >= '$DATE_START_SELECTED'
AND end_datetime < '$DATE_END_SELECTED' 
AND events.VENUE_LOCATION IS NULL

Upvotes: 1

Related Questions