<prepared name="groupgalaxies">
<param id="1" type="int" name="groupid" />
<transform name="htmlescape" infield="name" outfield="html_name" />
SELECT g.id,g.name,g.beginat,
sp.cnt AS sps,
csts.cnt AS csts,
lp.cnt AS colonables,
spa.cnt AS startables
FROM csvdevel.galaxies g
LEFT JOIN (SELECT cst.galaxyid,count(sp.*) AS cnt
FROM csvdevel.constellations cst
JOIN csvdevel.starsystems ss ON cst.id = ss.cstid
JOIN csvdevel.planets p ON ss.id = p.starsysid
LEFT JOIN csvdevel.startplanets sp ON p.id = sp.planetid
GROUP BY cst.galaxyid) sp ON g.id = sp.galaxyid
LEFT JOIN (SELECT cst.galaxyid,count(cst.*) AS cnt FROM csvdevel.constellations cst
GROUP BY cst.galaxyid) csts ON g.id = csts.galaxyid
LEFT JOIN (SELECT cst.galaxyid,count(p.*) AS cnt
FROM csvdevel.constellations cst
JOIN csvdevel.starsystems ss ON cst.id = ss.cstid
JOIN csvdevel.planets p ON ss.id = p.starsysid
JOIN csvdevel.planettypes pt ON p.ptypeid = pt.id
WHERE pt.colonable
GROUP BY cst.galaxyid) lp ON g.id = lp.galaxyid
LEFT JOIN (SELECT cst.galaxyid,count(p.*) AS cnt
FROM csvdevel.constellations cst
JOIN csvdevel.starsystems ss ON cst.id = ss.cstid
JOIN csvdevel.planets p ON ss.id = p.starsysid
JOIN csvdevel.planettypes pt ON p.ptypeid = pt.id
WHERE pt.startable
GROUP BY cst.galaxyid) spa ON g.id = spa.galaxyid
WHERE g.groupid= $1
ORDER BY ldeaccent_utf8(g.name);
</prepared>
<prepared name="getunusedgroupgalaxies">
<param id="1" type="int" name="groupid" />
<transform name="htmlescape" infield="name" outfield="html_name" />
SELECT id,name
FROM csvdevel.galaxies
WHERE groupid = $1 AND NOT beginat
AND NOT id IN (SELECT g.id
FROM csvdevel.galaxies g
JOIN csvdevel.constellations cst ON cst.galaxyid = g.id
JOIN csvdevel.starsystems ss ON ss.cstid = cst.id
JOIN csvdevel.planets p ON p.starsysid = ss.id
WHERE p.id IN (SELECT planetid FROM csvdevel.colonies));
</prepared>