Select records based on count of other records-CodePudding

I have a data table like

ID category
1  1
2  1
3  2
4  2
5  4

I wish to select all records that fall into two given categories i and j (for instance i=1 and j=4 would return records 1, 2 and 5). But here's the twist: if one of the of the categories has no records, the query should return an empty set. For instance if I query for i=1 and j=3. Can this be done, solely using SQL?

I've been trying to make an intermediate count table, but I'm afraid my sql isn't up to par.

CodePudding user response：

Just write down what you said, will give:

SELECT *
FROM table1
WHERE (SELECT count(*) FROM table1 WHERE category=1)>0 
  AND (SELECT count(*) FROM table1 WHERE category=4)>0

see: DBFIDDLE

or, slightly different:

SELECT *
FROM table1
WHERE (SELECT count(distinct category) FROM table1 WHERE category IN (1,4))=2

P.S. I choose to use mysql in the DbFiddle, but this should work in other DBMS's too.

EDIT: Also filtering on that category should be done:

SELECT *
FROM table1
WHERE category IN (1,4)
  and (SELECT count(distinct category) FROM table1 WHERE category IN (1,4))=2

CodePudding user response：

have you tried SELECTing all WHERE the record is not NULL? Try it and respond if it did not work.
Try only SELECTing the 1s and 4s for example in your table.

CodePudding user response：

you can try for SQL CASE expression. And accordingly you can put your query in the CASE expression.