Mysql query to check if all sub_items of a combo_item are active

I am trying to write a query that looks through all combo_items and only returns the ones where all sub_items that it references have Active=1.

I think I should be able to count how many sub_items there are in a combo_item total and then compare it to how many are Active, but I am failing pretty hard at figuring out how to do that...

My table definitions:

CREATE TABLE `combo_items` (
  `c_id` int(11) NOT NULL,
  `Label` varchar(20) NOT NULL,
  PRIMARY KEY (`c_id`)
)

CREATE TABLE `sub_items` (
  `s_id` int(11) NOT NULL,
  `Label` varchar(20) NOT NULL,
  `Active` int(1) NOT NULL,
  PRIMARY KEY (`s_id`)
)

CREATE TABLE `combo_refs` (
  `r_id开发者_StackOverflow社区` int(11) NOT NULL,
  `c_id` int(11) NOT NULL,
  `s_id` int(11) NOT NULL,
  PRIMARY KEY (`r_id`)
)

So for each combo_item, there is at least 2 rows in the combo_refs table linking to the multiple sub_items. My brain is about to make bigbadaboom :(

I would just join the three tables usually and then combo-item-wise sum up the total number of sub-items and the number of active sub-items:

SELECT ci.c_id, ci.Label, SUM(1) AS total_sub_items, SUM(si.Active) AS active_sub_items
FROM combo_items AS ci
INNER JOIN combo_refs AS cr ON cr.c_id = ci.c_id
INNER JOIN sub_items AS si ON si.s_id = cr.s_id
GROUP BY ci.c_id

Of course, instead of using SUM(1) you could just say COUNT(ci.c_id), but I wanted an analog of SUM(si.Active).

The approach proposed assumes Active to be 1 (active) or 0 (not active).

To get only those combo-items whose all sub-items are active, just add WHERE si.Active = 1. You could then reject the SUM stuff anyway. Depends on what you are looking for actually:

SELECT ci.c_id, ci.Label
FROM combo_items AS ci
INNER JOIN combo_refs AS cr ON cr.c_id = ci.c_id
INNER JOIN sub_items AS si ON si.s_id = cr.s_id
WHERE si.Active = 1
GROUP BY ci.c_id

By the way, INNER JOIN ensures that there is at least one sub-item per combo-item at all.

(I have not tested it.)

See this answer: MySQL: Selecting foreign keys with fields matching all the same fields of another table

Select ...
From combo_items As C
Where Exists    (
                Select 1
                From sub_items As S1
                    Join combo_refs As CR1
                        On CR1.s_id = S1.s_id
                Where CR1.c_id = C.c_id
                )
    And Not Exists  (
                    Select 1
                    From sub_items As S2
                        Join combo_refs As CR2
                            On CR2.s_id = S2.s_id
                    Where CR2.c_id = C.c_id
                        And S2.Active = 0
                    )

The first subquery ensures that at least one sub_item exists. The second ensures that none of the sub_items are inactive.