Please help convert a subselect to inner join 1

[maddruid]

I was developing a php/postuke app for a client and I wrote two of my SQL queries with subselects. I found out after I was done that they were pretty much stuck with MySQL 4.0.x for awhile, so I need to revamp my queries to avoid subselects. The query uses three tables:

nuke_gwbt_guild_halls
nuke_gwbt_guild_halls_notes
nuke_gwbt_matches

I am getting all of the fields in the first table, matching the notes id from the second table to a notes id in the first table, and then counting some metrics from the third table to return as fields in the resulting recordset, used for ORDER BY sorting. Here is the working subselect query:

SELECT nuke_gwbt_guild_halls.*, `nuke_gwbt_guild_halls_notes.fulltext`,
(SELECT COUNT(*) FROM `nuke_gwbt_matches` WHERE `nuke_gwbt_matches.hall_id` = `nuke_gwbt_guild_halls.ghid`) AS total,
(SELECT COUNT(*) FROM `nuke_gwbt_matches` WHERE `nuke_gwbt_matches.hall_id` = `nuke_gwbt_guild_halls.ghid` AND `nuke_gwbt_matches.winlose` > 0) AS wins,
(SELECT `total` - `wins`) AS losses,
(SELECT COUNT(*) FROM `nuke_gwbt_matches` WHERE `nuke_gwbt_matches.hall_id` = `nuke_gwbt_guild_halls.ghid` AND `nuke_gwbt_matches.err` > 0) AS err
FROM `nuke_gwbt_guild_halls`
LEFT JOIN `nuke_gwbt_guild_halls_notes` ON
`nuke_gwbt_guild_halls.ghnotes_id` = `nuke_gwbt_guild_halls_notes.gnid`

I've been able to convert most of it to a query with no subselects, but I can't figure out how to get "losses". Here's the new query, mostly working:
SELECT nuke_gwbt_guild_halls.*, `nuke_gwbt_guild_halls_notes.fulltext`, COUNT(`nuke_gwbt_matches.winlose`) as total, SUM(`nuke_gwbt_matches.winlose`) as wins, SUM(`nuke_gwbt_matches.err`) as err
FROM `nuke_gwbt_guild_halls` INNER JOIN `nuke_gwbt_matches` ON `nuke_gwbt_guild_halls.ghid` = `nuke_gwbt_matches.hall_id`
LEFT JOIN `nuke_gwbt_guild_halls_notes` ON
`nuke_gwbt_guild_halls.ghid` = `nuke_gwbt_guild_halls_notes.gnid`
GROUP BY `nuke_gwbt_guild_halls.ghid`

I know I can calculate losses by subtracting wins from total using php, but I want it as part of my recordset so I can use ORDER BY losses in the initial query, using the more highly optimized DB sorting over sorting a php array after the results are returned. If anyone can help me, I would greatly appreciate it.

Thanks

 

[maddruid]

Actually, I realized I need a LEFT OUTER JOIN instead of an INNER JOIN, but my losses problem is still the same.

 

[r937]

can a guild hall have more than one guild hall note and at the same time more than one match?

if so, your counts will be wrong if you join all three tables

r937.com | rudy.ca

 

[maddruid]

No. A Guild hall can only have one guild note, but there will be many matches associated with it.

 

[r937]

SELECT H.ghid
     , N.fulltext
     , COUNT(M.winlose) as total
     , SUM(M.winlose) as wins
     , COUNT(M.winlose) 
      -SUM(M.winlose) as losses
     , SUM(M.err) as err
  FROM nuke_gwbt_guild_halls as H
INNER 
  JOIN nuke_gwbt_matches as M
    ON M.hall_id = H.ghid 
LEFT 
  JOIN nuke_gwbt_guild_halls_notes as N
    ON N.gnid = H.ghid 
GROUP 
    BY H.ghid
     , N.fulltext
order
    by losses desc

note that you should not use "select star" with GROUP BY -- just put every non-aggregate column into both the SELECT and the GROUP BY

r937.com | rudy.ca

 

[maddruid]

Thanks! I'll give this a shot. I had already coded it with the php sort when a user selected losses in the drop-down, but it was noticably slower than the other sorts. This should speed it up considerably. I appreciate the help.