mySQL - SELECT using JOIN, or a nested SELECT (sub-select)?

Question

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?

Can you dump the SQL for your setup so I can have a fiddle in a local environment to see where things are going wrong? Pastebin is ideal. — Eamonn, Jun 01 '17 at 08:07

score 1 · Answer 1 · answered May 31 '17 at 11:21

1

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

Simple subquery.

answered May 31 '17 at 11:21

Eamonn

418
3
11

I have tried it, but it didn't work. I even tried adding an index to the WhoAlreadyClickedImg table (as @e4c5 suggested). This is the error it gives me: "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" – marble Jun 01 '17 at 04:06

score 1 · Answer 2 · answered Jun 01 '17 at 09:52

1

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.

answered Jun 01 '17 at 09:52

Nadir Latif

3,690
1
15
24

got to a lot of rows in the table that is being called by the sub-select. and it is VERY slow sometimes and sometimes it is ok. (when I test it and empty this table it is always fast) - any suggestion? maybe JOIN would be better or is JOIN not the solution and the select is simply done wrong? – marble Jun 18 '17 at 20:53
You should add an index on these columns: Index, ImageIndex and UserWhoRankedIt. It should improve performance of the sql query – Nadir Latif Jun 19 '17 at 06:51
You mean that If I add an index to the "WhoAlreadyClickedMyImg" table, EVEN IF I TOTALY IGNORE IT in my query, it will help the speed? How?! – marble Jun 19 '17 at 09:36
And also - what do you mean by saying "add index to all those three columns"? You mean - define each one as Index? – marble Jun 19 '17 at 09:38
Well, yes. Add an index to all three columns. Most database administration tools such as phpMyAdmin support adding indexes. When you add an index to a column, the database server stores the contents of that column in a special format which reduces the time needed to search the column – Nadir Latif Jun 19 '17 at 11:28

score 1 · Accepted Answer · answered Jun 02 '17 at 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.

mySQL - SELECT using JOIN, or a nested SELECT (sub-select)?

I checked my system:

3 Answers3