CODEWITHSUNDEEP
mysqlsql
sdafasdf
sdafasdf

Reputation: 99

SQL select rows with filters

I have the following table:

+----+----------+
| id | feature  |
+----+----------+
| 1  |   10     |
| 1  |   20     |
| 2  |   20     |
| 3  |   40     |
| 4  |   50     |
| 5  |   60     |
+----+----------+

And I'd like to have the id's that have both features 10 and 20. So, not just the results that have 10 or 20, but I'd like to have the result of the id's that have both 10 and 20.

Upvotes: 0

Views: 55

Answers (3)

user958802
user958802

Reputation: 63

One more way to do that

SELECT ID,
stuff(
(
    select ','+ convert(varchar,feature) from table where Id = t.Id for XML path('')
),1,1,'') [comma_sep_feature]
FROM [table] t 
group by id

you can insert this value in #temp and add where clause comma_sep_feature = '10, 20' like below

SELECT ID,
        stuff(
        (
            select ','+ convert(varchar,feature) from yourtable where Id = t.Id for XML path('')
        ),1,1,'') [comma_sep_feature]
        into #tem
        FROM [yourtable] t 
        group by id

    select * from #tem where comma_sep_feature = '10,20'

    drop table #tem

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

The easiest way would be to GROUP BY the id and use HAVING:

SELECT id
FROM table_name
WHERE feature IN (10,20)
GROUP BY id
HAVING COUNT(distinct feature) = 2

Upvotes: 3

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

Another way to select the id that have features 10 and 20 you can do so 

select id
from table1
group by id
having sum(feature = 10)
and sum(feature = 20)

DEMO

Upvotes: 2

Related Questions