SitePoint Sponsor

User Tag List

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

    Same value in table for different rows

    This is probably straight forward, but say I had a table:

    Code:
    person_id  surname  console  game
    ====================================
    1          jones    xbox360  cod4
    2          bloggs   ps3      cod4
    3          smith    wii      bowling
    4          another  xbox360  cod4
    If I wanted to execute a query asking if the table has the same game on both xbox360 and ps3, how would I go about doing that? So the condition would be TRUE for the table above because of rows 1 and 2 (xbox360 and ps3=cod4).

    Any advice appreciated.
    neil - Geocities, the trailer park of the web
    .<.<.<.<.<.<.<.<
    WEB[retype.net] + [guff.org]
    ICQ[273981] AIM[neiim]

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,272
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT game
      FROM daTable
     WHERE console IN ( 'xbox360','ps3' )
    GROUP
        BY game
    HAVING COUNT(DISTINCT console) > 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    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 r937 View Post
    Code:
    SELECT game
      FROM daTable
     WHERE console IN ( 'xbox360','ps3' )
    GROUP
        BY game
    HAVING COUNT(DISTINCT console) > 1
    That works great, however I tried adding surname=bloggs into the criteria but it didn't result in anything... I'm guessing you cannot do multiple where clauses in this fashion?
    Code:
    SELECT game
      FROM daTable
     WHERE surname='bloggs' AND console IN ( 'xbox360','ps3' )
    GROUP
        BY game
    HAVING COUNT(DISTINCT console) > 1
    neil - Geocities, the trailer park of the web
    .<.<.<.<.<.<.<.<
    WEB[retype.net] + [guff.org]
    ICQ[273981] AIM[neiim]

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,272
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    of course you can

    the reason it didn't return anything is because bloggs doesn't have both platforms
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Captain Internet neil's Avatar
    Join Date
    Jun 2001
    Location
    n.ireland
    Posts
    333
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What if he owns either one, say the ps3 for example? Then mister bloggs can see what other platforms have cod4, doesn't make much sense I know but just curiousity sake
    neil - Geocities, the trailer park of the web
    .<.<.<.<.<.<.<.<
    WEB[retype.net] + [guff.org]
    ICQ[273981] AIM[neiim]

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,272
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    no idea what you're asking, sorry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Captain Internet neil's Avatar
    Join Date
    Jun 2001
    Location
    n.ireland
    Posts
    333
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's okay, not many people do.

    I was just wondering whether surname=bloggs could be used as part of the criteria in the query you gave previously. The query would result in TRUE if bloggs was a surname in any one of the results (not necessarily all).

    This is valid:
    Code:
    person_id  surname  console  game
    ====================================
    1          jones    xbox360  cod4
    2          bloggs   ps3      cod4
    3          smith    wii      bowling
    4          another  xbox360  cod4
    This is invalid using smith instead of bloggs:
    Code:
    person_id  surname  console  game
    ====================================
    1          jones    xbox360  cod4
    2          bloggs   ps3      cod4
    3          smith    wii      bowling
    4          another  xbox360  cod4
    neil - Geocities, the trailer park of the web
    .<.<.<.<.<.<.<.<
    WEB[retype.net] + [guff.org]
    ICQ[273981] AIM[neiim]

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,272
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT game
      FROM daTable
     WHERE console IN ( 'xbox360','ps3' )
    GROUP
        BY game
    HAVING COUNT(DISTINCT console) > 1
       AND COUNT(CASE WHEN surname='bloggs' THEN 'yep' END) > 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    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 r937 View Post
    Code:
    SELECT game
      FROM daTable
     WHERE console IN ( 'xbox360','ps3' )
    GROUP
        BY game
    HAVING COUNT(DISTINCT console) > 1
       AND COUNT(CASE WHEN surname='bloggs' THEN 'yep' END) > 0
    Again, works great... haven't been on these forums in a while, but safe to say you've earned that title. Thumbs up here.
    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
  •