Same value in table for different rows

[neil]

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

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.

[r937]

SELECT game
  FROM daTable
 WHERE console IN ( 'xbox360','ps3' )
GROUP
    BY game
HAVING COUNT(DISTINCT console) > 1

[neil]

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?

SELECT game
  FROM daTable
 WHERE surname='bloggs' AND console IN ( 'xbox360','ps3' )
GROUP
    BY game
HAVING COUNT(DISTINCT console) > 1

[r937]

of course you can

the reason it didn't return anything is because bloggs doesn't have both platforms

[neil]

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

[r937]

no idea what you're asking, sorry

[neil]

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:

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:

person_id  surname  console  game
====================================
1          jones    xbox360  cod4
2          bloggs   ps3      cod4
3          smith    wii      bowling
4          another  xbox360  cod4

[r937]

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

[neil]

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.