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

Question

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!

Aurimas · Accepted Answer · 2012-02-06 09:33:24Z

1

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

answered Feb 6, 2012 at 9:33

Aurimas

2,49318 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brad Over a year ago

I completely understand what you'r saying. I knew that, I just didn't know how to deal with it. I did find a way to solve it, but yours looks much more elegant. I will try this later tonight. Thanks!

ron tornambe · Accepted Answer · 2012-02-05 22:54:32Z

0

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

answered Feb 5, 2012 at 22:54

ron tornambe

10.8k8 gold badges38 silver badges61 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related