CODEWITHSUNDEEP
mysqlsql
meder omuraliev
meder omuraliev

Reputation: 186552

Subquery necessary for comparison?

Goal:

Need results for channels that are active (channels.active=1) but have 0 active items.

There are items and they belong in channel. items can have multiple channels.

There are 3 tables:

The items_channel maps items to channels. Example row:

id  item   channel
1   23     47

So far I just have this to get active channels:

SELECT * FROM channels WHERE channels.active = 1

I'm having trouble grasping the part where I only join the channels that have 0 active items - do I need a subquery or a HAVING clause that has a COUNT somewhere?

Also, channels.active is for active channels, and items.active is for active items.

Upvotes: 0

Views: 70

Answers (2)

semao
semao

Reputation: 1757

Is this what you are looking for?

SELECT * FROM channels c
    LEFT OUTER JOIN items_channels ic ON c.channelID = ic.channelID
    LEFT OUTER JOIN items i ON i.itemID = ic.itemID AND i.active = 1
WHERE channels.active = 1 AND i.itemID IS NULL

If you use COUNT() you would need to use nested queries like:

SELECT * FROM channels c
WHERE channels.active = 1 AND c.channelID IN 
    (
    SELECT ic.channelID FROM items_channels ic 
        LEFT OUTER JOIN items i ON i.itemID = ic.itemID
        WHERE i.active = 1
    GROUP BY ic.channelID
    HAVING COUNT(ic.itemID) == 0
    )

Upvotes: 3

PinnyM
PinnyM

Reputation: 35533

This should work for you:

SELECT channels.* 
FROM channels
LEFT JOIN items_channels
  ON channels.id = items_channels.channel
LEFT JOIN items
  ON items_channels.item = items.id AND items.active = 1
WHERE channels.active = 1
GROUP BY channels.id
HAVING COUNT(items.id) = 0

Working sqlfiddle here.

Upvotes: 2

Related Questions