SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Captain Internet neil's Avatar
    Join Date
    Jun 2001
    Location
    n.ireland
    Posts
    333
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Querying partial values, but not identical

    I'm attempting to get the list of personid's which has two different TYPE%'s (without it being the same type).

    See the table below, personid 100 would satisfy because of TYPE1 and TYPE2, but personid 200 would not because of only having TYPE1 twice.
    Code:
    columnid | personid | type
    1        | 100      | TYPE1
    2        | 100      | TYPE2
    3        | 100      | TYPE1
    4        | 200      | HELLO
    5        | 200      | TYPE1
    6        | 200      | TYPE1
    7        | 101      | WORLD
    I'm working towards using the GROUP BY statement, this obviously catches the personid's with the same TYPE as mentioned above. Is there any way to add a distinct of some kind so that doesn't happen?
    Code:
    SELECT typetable.personid
    FROM typetable
    WHERE typetable.type like 'TYPE%'
    GROUP BY typetable.personid
    HAVING count(*) > 1
    neil - Geocities, the trailer park of the web
    .<.<.<.<.<.<.<.<
    WEB[retype.net] + [guff.org]
    ICQ[273981] AIM[neiim]

  2. #2
    Captain Internet neil's Avatar
    Join Date
    Jun 2001
    Location
    n.ireland
    Posts
    333
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nevermind, using a WHERE IN seems to work...
    Code:
    WHERE typetable.type in ('TYPE1','TYPE2')
    Just thought there might have been a way without specifically defining each of the types.
    neil - Geocities, the trailer park of the web
    .<.<.<.<.<.<.<.<
    WEB[retype.net] + [guff.org]
    ICQ[273981] AIM[neiim]

  3. #3
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    695
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select personid
      from typetable
     where type like 'TYPE%'
     group by personid
    having count(distinct type) > 1

  4. #4
    Captain Internet neil's Avatar
    Join Date
    Jun 2001
    Location
    n.ireland
    Posts
    333
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by swampBoogie View Post
    Code:
    select personid
      from typetable
     where type like 'TYPE%'
     group by personid
    having count(distinct type) > 1
    Excellent, thank you! I was trying to put the DISTINCT inside the SELECT.
    neil - Geocities, the trailer park of the web
    .<.<.<.<.<.<.<.<
    WEB[retype.net] + [guff.org]
    ICQ[273981] AIM[neiim]

  5. #5
    Captain Internet neil's Avatar
    Join Date
    Jun 2001
    Location
    n.ireland
    Posts
    333
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just out of interest, whenever I create a JOIN to the person table it expands the results for some reason (so it's no longer accurate). Is there a problem joining a table while using a GROUP BY like this?

    Code:
    SELECT typetable.personid
    FROM typetable
    JOIN person on person.personid = typetable.personid
    WHERE typetable.type like 'TYPE%'
    GROUP BY typetable.personid
    HAVING count(distinct type) > 1

    Disregard this... :-)
    Last edited by neil; Jul 7, 2011 at 08:26. Reason: Forgot to add a \ at the end of the line (working in unix).
    neil - Geocities, the trailer park of the web
    .<.<.<.<.<.<.<.<
    WEB[retype.net] + [guff.org]
    ICQ[273981] AIM[neiim]


Bookmarks

Posting Permissions

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