Alright, I've got a little bit of a problem with this query.
This is what the current query looks like ( I know I'm doing it wrong, I
just don't know how to fix it.. I'm not that familiar with the LEFT
JOIN/subquery stuff.. )
select cluster.cid, cluster.isp, cluster.country_id-200000 as
Country_ID, cluster.site_id,
-> count(compaq.id) as Compaq,
-> count(foundry.id) as Foundry,
-> count(netapp.id) as Netapp,
-> count(cisco.id) as Cisco
-> from cluster LEFT JOIN compaq on cluster.cid = compaq.cid and
compaq.active="Y"
-> LEFT JOIN netapp ON cluster.cid = netapp.cid and
netapp.active="Y"
-> LEFT JOIN foundry ON cluster.cid = foundry.cid and
foundry.active="Y"
-> LEFT JOIN cisco ON cluster.cid = cisco.cid and cisco.active="Y"
-> where ( cluster.ctype="Streaming" and cluster.active="Y" )
-> group by cluster.cid;
Basically I have a 'cluster' table, with a 'cid' key. Then I have 4
equipment tables ( foundry, netapp, cisco, compaq ) that link back to
the cluster table via the 'cid'.
What I'd like is to get all the clusters and a count of each piece of
ACTIVE equipment that links to it.. ( we have equipment linked to the
cluster table that is considered inactive.. I do not want to include
those in the query. )
Something that would look like this -
+-----+-----------+----------+-------+------+-------+------+-------+
| cid | isp |Country_ID|site_id|Compaq|Foundry|Netapp| Cisco |
+-----+----------------------+-------+------+-------+------+-------+
| 287 | Concentric| 200| 31 | 4 | 2 | 1 | 1 |
The problem is I end up with something like this -
+-----+-----------+----------+-------+------+-------+------+-------+
| cid | isp |Country_ID|site_id|Compaq|Foundry|Netapp| Cisco |
+-----+----------------------+-------+------+-------+------+-------+
| 287 | Concentric| 200| 31 | 8 | 8 | 8 | 8 |
It seems to be combining all the counts. I can see *why* it's doing
that in the query, but I'm not sure what the correct statement would be
to fix this? I could do this with perl/dbi, but I'm trying to stay away
from doing 5 queries to get this information.
Any help appreciated.
Thanks,
sh
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
