CODEWITHSUNDEEP
mysqlsql
Happy Coder
Happy Coder

Reputation: 4682

Select value which don't have atleast one association

I have a table ItemCategory with category listing and this category is assigned with items. It is like the following:

+---------+-------------+
| item_id | category_id |
+---------+-------------+
|       1 |           1 |
|       2 |           1 |
|       3 |           0 |
|       3 |           2 |
|       3 |           8 |
|       4 |           0 |
|       5 |           0 |
+---------+-------------+

Now I need to get the items which don't have any category values. That is in this case it is 4 and 5 which have category as zero. But not not as it is assigned with atleast one category.

I am actually joining these with another tables called Networks and Items so I use query something like this. 

SELECT Network.networkname,Items.item_id,ItemCategory.catname 
FROM Network 
JOIN Items 
ON Items.networkid=network.networkid 
JOIN ItemCategories 
ON ItemCategory.item_id=Item.item_id

Upvotes: 1

Views: 83

Answers (3)

Ankur Trapasiya
Ankur Trapasiya

Reputation: 2200

Try this sql.

select item_id
from Table
group by item_id
having max(category_id)=0
and count(item_id)=1

Upvotes: 0

Bere
Bere

Reputation: 1747

select * from a group by item having count(distinct category)=1 and category=0;

Upvotes: 2

Himanshu
Himanshu

Reputation: 32602

Try this one:

SELECT * FROM Table1
WHERE item_id IN ( 
                   SELECT item_id FROM Table1
                   GROUP BY item_id
                   HAVING MAX(category_id) = 0
                 )

Result:

╔═════════╦═════════════╗
║ ITEM_ID ║ CATEGORY_ID ║
╠═════════╬═════════════╣
║       4 ║           0 ║
║       5 ║           0 ║
╚═════════╩═════════════╝

See this SQLFiddle

You can use DISTINCT keyword if you don't want duplicate rows in the result:

SELECT DISTINCT * FROM Table1
WHERE item_id IN ( 
                   SELECT item_id FROM Table1
                   GROUP BY item_id
                   HAVING MAX(category_id) = 0
                 );

See this SQLFiddle for more details.

Upvotes: 2

Related Questions