CODEWITHSUNDEEP
sqloracle-databasegroup-byoracle10g
user178822
user178822

Reputation: 53

SQL query for fetching a single column with multiple values

Consider the below table:

Table1

id | status
------------
1  | A
2  | B
3  | C
1  | B
4  | B
5  | C
4  | A

Desired output is 1 and 4 as they are having status as both 'A' and 'B'.

Can we write an query for this? I tried to query it using conditions like 'AND', 'UNION' and 'OR', but it did not return me the desired result.

Upvotes: 0

Views: 1743

Answers (3)

zealous
zealous

Reputation: 7503

Try this one, you can do without using having() as well

select
    id
from
(
    select
        id,
        count(distinct status) as cnt
    from yourTable
    group by
        id
) val
where cnt > 1

Upvotes: 0

forpas
forpas

Reputation: 164089

If you want the ids with more than 1 statuses:

select id
from tablename
group by id
having count(distinct status) > 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can use aggregation:

select id
from t
where status in ('A', 'B')
group by id
having count(*) = 2;

If the table allows duplicates, then use count(distinct status) = 2.

Upvotes: 0

Related Questions