Link to home
Start Free TrialLog in
Avatar of phper
phper

asked on

LEFT JOIN WHERE DATE > NOW Problem

I have a table of venues and a table of shows. The shows table include shows that are older then NOW(). I'm trying to show all the venues in alphbetically order and then show any shows at the venue that are greater than now.

I can LEFT JOIN the two tables. But if I include DATE(show_date) >= NOW() it excludes venues that where show dates have past. So I took out the 'DATE(show_date) >= NOW() ' and added a 'IF > Now' in the page. But because I'm GROUP BY venue name  the query is selecting the first show date, so now no shows are showing.

What I trying to do is:
venues LEFT JOIN shows ON ... WHERE show date >= now. But with all of the venues showing. Here is my current query. Also this query is the one for the page that shows all venues begining with a number.
SELECT venues.vid,venues.VenName,
shows.shband,
shows.shbid,
venues.VenAddress,
venues.VenCity, 
DATE_FORMAT(shows.shdate, '%c/%e %l:%i%p') AS shdatef, 
shows.shdate
FROM venues LEFT Join shows ON venues.vid = shows.shvid WHERE (venues.VenName LIKE '0%' OR venues.VenName LIKE '1%' OR venues.VenName LIKE '2%' OR venues.VenName LIKE '3%' OR venues.VenName LIKE '4%' OR venues.VenName LIKE '5%' OR venues.VenName LIKE '6%' OR venues.VenName LIKE '7%' OR venues.VenName LIKE '8%' OR venues.VenName LIKE '9%')  AND shows.shsame is null GROUP BY venues.VenName ORDER BY venues.VenName, shows.shdate ASC

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of dsacker
dsacker
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial