mysql - 提取具有两个值的记录

Question

我有一个查询问题。

结果集:

SELECT players.codsw,sessions.userid,sessions.idlocation,location.denomination
from sessions 
LEFT JOIN location ON location.idlocation = sessions.idlocation
LEFT JOIN players ON players.userid = sessions.userid
GROUP BY sessions.userid,sessions.idlocation
    HAVING players.codsw IN ('MEGA SHOW','ANCORINA')
ORDER BY location.denomination

我只需要提取同时具有 codsw 的记录，在本例中为 MEGA SHOW 和 ANCORINA 以及相同的 idlocation。

如果您看到我的结果集，它会显示第一个或第二个 codsw 的所有 idlocation，而不是两者。

我希望结果只看到 idlocation 34 或 623 而不是另一个。

非常感谢

里卡多

Answer 1

试试这个:

SELECT players.codsw
      ,sessions.userid
      ,sessions.idlocation
      ,location.denomination
  FROM sessions sess
      ,location loc
      ,players  pl
 WHERE location.idlocation = sessions.idlocation
   AND players.userid = sessions.userid
   AND players.codsw IN ('MEGA SHOW', 'ANCORINA')
   AND EXISTS (SELECT sessions.idlocation
              ,COUNT(*)
          FROM sessions
              ,location
              ,players
         WHERE location.idlocation = sessions.idlocation
           AND players.userid = sessions.userid
           AND players.codsw IN ('MEGA SHOW', 'ANCORINA')
           AND sessions.idlocation = sess.idlocation
         GROUP BY sessions.idlocation
        HAVING COUNT(*) > 1)
 ORDER BY location.denomination