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.

Code:

---

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

---

Quote:

---

Originally Posted by r937

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

---

of course you can :)

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

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 :inspector:

no idea what you're asking, sorry

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

---

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

---

Quote:

---

Originally Posted by r937

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.