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
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?
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.
SELECT * FROM Images WHERE Index NOT IN (SELECT ImageIndex FROM WhoAlreadyClickedImg WHERE UserWhoRankedIt = 93);
Simple subquery.
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.