> "Keith" <[EMAIL PROTECTED]> wrote:
> had to upgrade mySQL but I got the subquery working for individual entries.
> Trouble now is that I can't figure out how to get it to work for more than
> one entry. The query currently stands at:
>
> SELECT SQL_CALC_FOUND_ROWS sys.sectorID, sys.name AS sysName, sys.sysID,
> sys.galX, sys.galY, SUBSTRING_INDEX(sys.description,' ',50) AS description,
> sec.name AS secName, pla.government FROM planets AS pla, systems AS sys,
> sectors AS sec WHERE sys.sectorID IN
> ('1','2','3','4','11','14','18','25','28','29') AND sys.visible='Y' AND
> sys.sectorID=sec.sectorID AND population=(SELECT MAX(population) FROM
> planets AS pla, systems AS sys, sectors AS sec WHERE sys.sectorID IN
> ('1','2','3','4','11','14','18','25','28','29') AND pla.sysID=sys.sysID AND
> sys.visible='Y' AND sys.sectorID=sec.sectorID) ORDER BY sys.name, sec.name
> LIMIT 0,10
>
> with that it will only display a single government. Where I want it to, for
> each system, look through that systems planets and find the highest
> population then return that. Any ideas?
You can rewrite your query like:
SELECT SQL_CALC_FOUND_ROWS sys.sectorID, sys.name AS sysName, sys.sysID,
sys.galX, sys.galY, SUBSTRING_INDEX(sys.description,' ',50) AS description,
ec.name AS secName, pla.government
FROM planets AS pla, systems AS sys, sectors AS sec
WHERE sys.sectorID IN ('1','2','3','4','11','14','18','25','28','29') AND
sys.visible='Y'
AND sys.sectorID=sec.sectorID
AND (pla.population, pla.sysID) IN (SELECT MAX(population), pla.sysID FROM
planets AS pla, systems AS sys, sectors AS sec
WHERE sys.sectorID IN ('1','2','3','4','11','14','18','25','28','29')
AND pla.sysID=sys.sysID AND sys.visible='Y' AND sys.sectorID=sec.sectorID
GROUP BY pla.sysID)
ORDER BY sys.name, sec.name
LIMIT 0,10
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
