Re: Desperate Sum(), Group by/Join question - One Step closer...

[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!

Peter

---------------------------------------------------------------------
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