java - 当值不在另一个表上时 SQLite 选择

Question

我有一个可以添加到 Activity 的玩家列表。我想选择尚未在事件列表中的玩家列表。我的查询是:

    stmt.executeQuery("SELECT PLAYER_NO,FIRST_NAME,SURNAME,"
                + "EMAIL,PHONE_NO FROM PLAYER"
                + "WHERE NOT EXISTS(SELECT PLAYER_NO FROM EVENT_PLAYER_LIST "
                + "WHERE EVENT_NO = "+eventKey+" & "
                + "EVENT_PLAYER_LIST.PLAYER_NO = PLAYER.PLAYER_NO);");

谁能发现问题或告诉我如何解决这个问题？

这是演变而来的两个表:

    String sql = " CREATE TABLE IF NOT EXISTS EVENT_PLAYER_LIST"
                    + " (EVENT_NO       INT     NOT NULL,"
                    + " PLAYER_NO       INT     NOT NULL,"
                    + " ACTIVE          INT,"
                    + " TIME_ENROLLED   CHAR(50),"
                    + " WINS            INT,"
                    + " LOSES           INT,"
                    + " DRAWS           INT,"
                    + " PRIMARY KEY (EVENT_NO, PLAYER_NO))";


    String sql = " CREATE TABLE IF NOT EXISTS PLAYER"
                    + " (PLAYER_NO       INTEGER     PRIMARY KEY    AUTOINCREMENT      NOT NULL,"
                    + " FIRST_NAME      CHAR(50)    NOT NULL,"
                    + " SURNAME         CHAR(50)    NOT NULL,"
                    + " DOB             CHAR(50),"
                    + " CLUB            CHAR(50),"
                    + " EMAIL           CHAR(120),"
                    + " PHONE_NO        CHAR(50))";

如果我在一个 Activity 中添加了多名玩家，除了第一个玩家之外，他们仍然会显示在玩家列表中。

Answer 1

我认为你的查询应该是:

stmt.executeQuery("SELECT PLAYER_NO,FIRST_NAME,SURNAME,"
                + "EMAIL,PHONE_NO FROM PLAYER"
                + "WHERE NOT EXISTS(SELECT PLAYER_NO FROM EVENT_PLAYER_LIST "
                + "WHERE EVENT_NO = "+eventKey+" AND "
                + "EVENT_PLAYER_LIST.PLAYER_NO = PLAYER.PLAYER_NO);");

请注意使用 AND 而不是 $。后者是Bitwise AND

请注意，构建查询的方式并不是最好的，通常建议您使用准备好的语句(出于安全和性能原因)