duanlujiaji10335
2017-05-31 11:18
浏览 1.0k
已采纳

mySQL - 使用JOIN的SELECT,还是嵌套的SELECT(子选择)?

I am trying to write a SELECT in mySQL (and PHP) that will retrieve all the rows in "Images" table that were not ranked yet by a certain user.

Those are my tables:

Table: Images

+-----------+----------+-----------+----------+
| Index     | Rank_Good| Rank_OK   | Rank_Bad |
+-----------+----------+-----------+-----------
| 201       | 2        | 9         | 28       |
| 202       | 11       | 20        | 39       |
| 203       | 36       | 14        | 7        |
+-----------+----------+-----------+----------+

Table2: WhoAlreadyClickedImg (has no index)

+------------+-----------------+-----------+
| ImageIndex | UserWhoRankedIt | RankGiven |
+------------+-----------------+-----------+
| 202        | 87              | OK        |
+------------+-----------------+-----------+
| 202        | 93              | Bad       |
+------------+-----------------+-----------+
| 204        | 93              | Good      |
+------------+-----------------+-----------+
| 203        | 94              | Bad       |
+------------+-----------------+-----------+

Every time a user rank an image, the table "Images" is updated and a row is added to "WhoAlreadyClickedImg" table. (this table has no index)

for example, if the user ranked image index 202 with "ok", then the col "Rank_OK" will be updated to (+1) and then, a new row will be added to the "WhoAlreadyClickedImg" table:

ImageIndex: 201 | UserWhoRankedIt: (the used session id) | RankGiven: OK

i want to build a select that will not show the same image twice to a user who already ranked it.

for example, if I'm user "93", the only image that the select will bring is "203"

UPDATED:

This is the query i'm using (by @eamonn):

SELECT * FROM Images WHERE Index NOT IN (SELECT ImageIndex FROM WhoAlreadyClickedImg WHERE UserWhoRankedIt = 93);

but I get an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Index NOT IN (SELECT ImageIndex FROM WhoAlreadyClickedImg WHERE UserWhoRankedIt' at line 1

I checked my system:

  • Storage Engine: InnoDB

  • MySQL db version: 5.5.33-29.3

  • both tables now have Index, int(11), defined as PRIMARY, auto_increment

maybe someone has an idea?

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • douji5746 2017-06-02 13:09
    已采纳

    There, this should work:

    SELECT Images.Index 
           FROM Images 
           WHERE Images.Index NOT IN 
                 (
                    SELECT WhoAlreadyClickedImg.ImageIndex 
                         FROM WhoAlreadyClickedImg 
                         WHERE WhoAlreadyClickedImg.UserWhoRankedIt = 93
                 );
    

    Remember to add the names of the tables before the name of the column.

    已采纳该答案
    打赏 评论
  • douye4051 2017-05-31 11:21

    SELECT * FROM Images WHERE Index NOT IN (SELECT ImageIndex FROM WhoAlreadyClickedImg WHERE UserWhoRankedIt = 93);

    Simple subquery.

    打赏 评论
  • douchan7552 2017-06-01 09:52

    Well the column name Index is a reserved keyword in MySQL. You should change the name of the Index column to Id or MainIndex. See this link: https://dev.mysql.com/doc/refman/5.6/en/keywords.html

    Another option is to use the table name inside the select query. For example:

    SELECT * FROM Images WHERE Images.Index NOT IN (SELECT ImageIndex FROM WhoAlreadyClickedImg WHERE UserWhoRankedIt = 93);
    

    I think you should rename your column since that minimizes the chances of errors in SQL statements.

    打赏 评论

相关推荐 更多相似问题