Limit HAVING without limiting SELECT 1

[maddruid]

Setup:
I have three tables that I am joining to display some simple statistics. The query I have no is working great, but I need to limit the aggregate functions by date.

The first table is ghall, which has some information about ghall objects. There are eight records in the table and it remains petty static.

The second table is ghnotes, which is just a table with an id and a fulltext field holding a note about each ghall object. The relationship is one-to-one. The id of this table maps to an ID field in the ghalls table.

The third table is a table of game matches. This is essentially the meat of my app and it holds a record of each match in a game. One of the fields is the id of the ghall the match is played in. The statistics are gathered from this table as well, including wins, losses, elapsed time, and errors for each match.

What I want to do is collect the statistics from the match table for a certain date range and return the results for each ghall object. If there are no records matching a certain ghall, I still want the data returned from the ghall table's record, but with a zero for each stat. When I add a HAVING clause to my query that specifies a date range, I get strange results. Here is the working query without a date range:

SELECT ghall.ghid,
ghall.name,
ghall.has_siege,
ghall.has_seed,
ghall.has_tele,
ghall.ghnotes_id,
ghall.image,
ghnotes.fulltext,
COUNT(matches.winlose) as total,
SUM(matches.winlose) as wins,
COUNT(matches.winlose) -
SUM(matches.winlose) as losses,
SUM(matches.err) as err
FROM ghall
LEFT JOIN matches ON
ghall.ghid = matches.hall_id
LEFT JOIN ghnotes ON
ghall.ghnotes_id = ghnotes.gnid
GROUP BY ghall.ghid

What I've tried (unsuccessfully) to do is add the following clause after adding the appropriate fields to the SELECT:

HAVING ((matches.match_date >= $start_date) AND (matches.match_date <= $end_date))

The results I get from this are incomprehensible to me. The numbers don't add up and only a subset of the ghalls records gets returned. I was under the impression that a LEFT join always returned all records from the left table, so I am assuming that my HAVING clause is affecting the left table before the join is being executed.

Any help is welcome.

 

[r937]

you are right about how a LEFT OUTER JOIN works, and indeed it is your HAVING clause that screws things up

SELECT ghall.ghid
     , ghall.name
     , ghall.has_siege
     , ghall.has_seed
     , ghall.has_tele
     , ghall.ghnotes_id
     , ghall.image
     , ghnotes.fulltext
     , COUNT(matches.winlose) as total
     , SUM(matches.winlose) as wins
     , COUNT(matches.winlose) -
       SUM(matches.winlose) as losses
     , SUM(matches.err) as err 
  FROM ghall
LEFT OUTER
  JOIN matches 
    ON matches.hall_id = ghall.ghid 
   [b]and[/b] matches.match_date >= $start_date
   [b]and[/b] matches.match_date <= $end_date
LEFT OUTER
  JOIN ghnotes 
    ON ghnotes.gnid = ghall.ghnotes_id
GROUP 
    BY ghall.ghid
     , ghall.name
     , ghall.has_siege
     , ghall.has_seed
     , ghall.has_tele
     , ghall.ghnotes_id
     , ghall.image
     , ghnotes.fulltext

r937.com | rudy.ca

 

[maddruid]

Thank you so much. My SQL inexperience shows here in that I never considered adding date conditions to the JOIN. It amazes me how obvious things are when someone shows you a solution.

Again, many thanks.