CODEWITHSUNDEEP
mysqlsql
Brad
Brad

Reputation: 11505

MySQL Count of related fields in a table from two other tables

I have four tables (fabrics, reservations and fabricmembers, nodes).

Each "node" may have have zero of more "fabricmembers" (associated with it). Each "fabric" may have zero or more "fabricmembers" (associated with it). A node may have a "reservation" on it, associated it with a single "node".

Thus you can see a references between these things when:

fabricmember.ipaddr = reservation.ipaddr

and

fabricmember.fabric = fabric.fabricname

(You can do all this without actually refering to the "node").

I want to run a single query that can show me for each FABRIC, how many (sum) RESERVATIONS are associated with it, and how many (sum) fabricmembers it has. Again, in a single query

I have been using the following query:

select 
   fabricmembers.fabric,
   count(reservations.ipaddr) as Memebers,
   count(nodes.ipaddr) as Reservations 
from fabricmembers  
LEFT JOIN (reservations,nodes) 
ON ((reservations.ipaddr = fabricmembers.ipaddr) and (nodes.ipaddr = fabricmembers.ipaddr))   
GROUP BY (fabricmembers.fabric);

And this almost works, however if a fabric has zero members, or it has members but those members have ZERO reservations, the fabric simply does not show up in the query.

The following works to show me how many members a fabric has, even if that number is zero:

select fabricname,count(fabricmembers.ipaddr) 
from fabrics 
LEFT JOIN (fabricmembers)
 ON (fabrics.fabricname = fabricmembers.fabric) 
GROUP BY (fabrics.fabricname);

however, I can't figure out how to have the query also tell me the number of members.

Does any of this make sense? Any help would be appreciated!

Upvotes: 1

Views: 128

Answers (2)

Aurimas
Aurimas

Reputation: 2493

Your query does not return fabrics with zero reservations / zero members, because you are trying to build it from tables where only fabrics with reservations / members exist! The only place you can find the empty fabrics is the fabrics table - thus you should build it from it downwards:

SELECT fabrics.fabricname, count(reservations.ipaddr), count(fabricmember.ipaddr)
FROM fabrics
LEFT JOIN fabricmembers ON fabrics.fabricname = fabricmembers.fabric
LEFT JOIN reservations ON fabricmembers.ipaddr = reservations.ipaddr
GROUP by fabric.fabricname

Upvotes: 1

ron tornambe
ron tornambe

Reputation: 10780

You need to use correlated sub-queries for this something like:

SELECT fabricmembers.fabric f, 
       (SELECT count(*) FROM reservations r where r.ipaddr = f.ipaddr) Members, 
       (SELECT count(*) FROM nodes n where n.ipaddr = f.ipaddr) Reservations
FROM fabricmembers

Upvotes: 0

Related Questions