Count and select from two tables

[blackhat]

I will change this topic, now I have a different problem that I can’t solve myself which is this.

SELECT COUNT(worlds.name) AS planets, COUNT(members.lname) AS players, sectors.id, sectors.name FROM sectors LEFT JOIN members ON members.sector = sectors.id LEFT JOIN worlds ON worlds.sector = sectors.id GROUP BY sectors.id

The count for players and planets appeas with the same amount. How can I fix that ?

[r937]

@blackhat

How can I fix that ?

run two queries

no, really

[blackhat]

I did eventually come up with this ? Its like you say, more queries I guess

SELECT sectors.id AS x, sectors.name, 
		(SELECT COUNT(name) FROM worlds WHERE worlds.sector = x) as planets,
		(SELECT COUNT(lname) FROM members WHERE members.sector = x) as members
FROM sectors GROUP BY sectors.name

[r937]

[quote=“blackhat, post:4, topic:216391, full:true”]
I did eventually come up with this ? [/quote]
correlated subqueries might perform poorly

two separate queries –

SELECT sectors.id , sectors.name , 'members' AS count_type , COALESCE(COUNT(members.sector),0) AS count_total FROM sectors LEFT OUTER JOIN members ON members.sector = sectors.id GROUP BY sectors.id , sectors.name

SELECT sectors.id , sectors.name , 'planets' AS count_type , COALESCE(COUNT(worlds.sector),0) AS count_total FROM sectors LEFT OUTER JOIN worlds ON worlds.sector = sectors.id GROUP BY sectors.id , sectors.name

here they are unioned together and sorted –

SELECT sectors.id , sectors.name , 'members' AS count_type , COALESCE(COUNT(members.sector),0) AS count_total FROM sectors LEFT OUTER JOIN members ON members.sector = sectors.id GROUP BY sectors.id , sectors.name UNION ALL SELECT sectors.id , sectors.name , 'planets' AS count_type , COALESCE(COUNT(worlds.sector),0) AS count_total FROM sectors LEFT OUTER JOIN worlds ON worlds.sector = sectors.id GROUP BY sectors.id , sectors.name ORDER BY sectors.id , sectors.name , count_type