Results 1 to 8 of 8
  1. #1
    Join Date
    May 2012
    Posts
    7

    Unanswered: SQL Query for finding items of 2 types

    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?

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    select ID from MyTable
    group by ID
    having COUNT(*) > 1
    Hope this helps.

  3. #3
    Join Date
    May 2012
    Posts
    7

    Question

    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.

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    select ID
    from (select distinct ID, typecol from MyTable) as t
    group by ID
    having COUNT(*) > 1
    Hope this helps.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    SELECT id
     FROM  MyTable
     GROUP BY
           id
     HAVING
           MIN(type) < MAX(type)
    ;

  6. #6
    Join Date
    May 2012
    Posts
    7

    Additional

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

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Are possible types c or v only?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •