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

Question

我有一个球员名单，可以添加到一个事件。 我想选择一个不在事件列表中的球员列表。 我的问题是：

    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))";

如果我有几个球员加入到一个事件中，他们都会出现在球员名单上，除了第一个

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);");

注意使用和代替$。后者是Bitwise AND

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