Hi Jörn,
I don't think you can do it in one SELECT
as you'll get the same number (the max)
as soon as the COUNT goes above zero.
If you think about how your resultset looks
if you remove your COUNTs it becomes clearer.
Say that for one a.a you have 3 matches in b
and 2 matches in c, this will result in three
rows. COUNT this and you will have an unpredictable result.
However you can solve it in one go with something like this:
SELECT Id, SUM(Bid), SUM(Cid)
FROM (SELECT a.Id AS Id, COUNT(b.Id) AS Bid, 0 AS Cid
FROM tablea a
LEFT JOIN tableb b ON b.a_ref=a.id
GROUP BY a.Id
UNION ALL
SELECT a.Id AS Id, 0 AS Bid, COUNT(c.Id) AS Cid
FROM tablea a
LEFT JOIN tablec c ON c.a_ref=a.id
GROUP BY a.Id
) AS tabled
GROUP BY Id
There is more then one select, but only one query...
/Johan
Jørn Dahl-Stamnes skrev:
I have a query like:
SELECT a.a,aa,COUNT(b.id),COUNT(c.id) FROM a LEFT JOIN b ON (b.a_ref=a.id)
LEFT JOIN c ON (c.a_ref=a.id);
But it seems like SQL is mixing up the two count's. I get the count from table
c instead of table b for the first occurence of COUNT in the query.
Can I use two COUNT's in one query or do I have to split the query into two
queries?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
