SQL Query for finding items of 2 types

learn4life · #1 (**permalink**) 05-10-12, 15:10

Have table of form:

ID name type
--- ------- ------
1 ad v
2 ba c
2 ba v
1 ad v
3 ca c
4 da v
1 ad c

Suppose I'd like to find the records in which an id has 2 or more type, which query could I use.

For example, id can be of type c or v. Which query would return the ids which had more than 1 type?

imex · #2 (**permalink**) 05-10-12, 15:31

Try:

Code:

select ID from MyTable
group by ID
having COUNT(*) > 1

Hope this helps.

learn4life · #3 (**permalink**) 05-10-12, 16:32

Thanks for the response. However, would the results include all records with duplicate types?

For ex,

ID name type
--- ------- ------
1 ad v
2 ba c
2 ba c
1 ad v
3 ca c
4 da v
1 ad c

I'd only want to return the ids that had more than one type of type. So in this case, ID 1 is the only ID associated with types c and v, so it should be the only result returned.

imex · #4 (**permalink**) 05-10-12, 16:49

Try:

Code:

select ID
from (select distinct ID, typecol from MyTable) as t
group by ID
having COUNT(*) > 1

Hope this helps.

tonkuma · #5 (**permalink**) 05-11-12, 02:46

Code:

SELECT id
 FROM  MyTable
 GROUP BY
       id
 HAVING
       MIN(type) < MAX(type)
;

learn4life · #6 (**permalink**) 05-11-12, 19:11

Now, what if I wanted to order the results, by ids that fell into, 1, or both of the categories and list these categories?

tonkuma · #7 (**permalink**) 05-12-12, 00:04

Now, completely different requirements.
Is it homework?

Try something by yourself, even if you couldn't get final results,
and show your trial queries, results of the queries and required results.
Then I would help you.

tonkuma · #8 (**permalink**) 05-12-12, 00:08

Are possible types c or v only?

Internet Services

Web Development

Digital Marketing

Consumer Tech