* Peter D Bethke > Ok, in regards to my previous dilemma, I've gotten it to: > > SELECT > golf_pick_periods.id, > golf_pick_periods.period_name, > golf_pick_periods.period_start_date, > golf_pick_periods.period_end_date, > > COUNT(DISTINCT golf_events.id) AS num_events, > COUNT(golf_player_picks.id) AS period_picks_count > > FROM > > database.golf_pick_periods > database.golf_player_picks > > LEFT JOIN > > database.golf_events > > ON > > (golf_events.event_start_date >= golf_pick_periods.period_start_date > > AND > > golf_events.event_end_date <= golf_pick_periods.period_end_date) > > WHERE > > golf_player_picks.picks_player_id = "1" > > AND > > golf_player_picks.picks_event_id = golf_events.id > > GROUP BY > > golf_pick_periods.id > > It's returning rows for periods where there is more than 1 pick (and > counting those picks too). I need it to return all the periods. It's > some kind of Left Join I'm sure but I can't figure how to reform the > query to use a left join to return rows where the number of picks in a > period is null. Arrg!
afaikt, you need to move the part of the WHERE-clause related to golf_events to the ON-clause of the LEFT JOIN: ... LEFT JOIN database.golf_events ON golf_player_picks.picks_event_id = golf_events.id AND golf_events.event_start_date >= golf_pick_periods.period_start_date AND golf_events.event_end_date <= golf_pick_periods.period_end_date WHERE golf_player_picks.picks_player_id = "1" GROUP BY golf_pick_periods.id HTH, -- Roger --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
