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

我有一个查询问题。

结果集：

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。

如果您看到我的结果集，则显示所有带有第一个或第二个idlocation的codsw，而不是全部显示。

我希望结果只能看到例如idlocation 34或623，而看不到其他。

非常感谢

里卡多

最佳答案

尝试这个：

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