MySQL Query to not select the same ID

Question

I have the query to select similar keywords within the database. However, the query is selecting its own ID as the one I'm trying to find similar recommendations for. I just want the recommendations and not the duplicated ID. I have tried '!=' and '<>' after productID, but this seems to break it.

So for this one, it will select ID 22 as well as its similar products. I don't want it picking 22 however.

`SELECT prodID FROM keywords WHERE prodID != 22 AND attributeID...` — miken32, Feb 02 '17 at 23:40
Though I'd bet you can do this much more efficiently with a join. — miken32, Feb 02 '17 at 23:40
Please edit your question and include the query you've written. Thanks. — Bob Jarvis - Слава Україні, Feb 03 '17 at 16:50

score 1 · Answer 1 · answered Feb 02 '17 at 23:41

1

It's not entirely clear to me what you've already tried; the description leaves a bit open to interpretation. I can say that this should work:

SELECT * 
  FROM products
 WHERE prodID IN (SELECT prodID 
                    FROM keywords 
                   WHERE attributeID IN (SELECT attributeID 
                                           FROM keywords a 
                                          WHERE prodID = 22
                                        )
                     AND prodID <> 22
                 )
 LIMIT 4;

If this is something you've tried and it didn't work, then you'll need to specify in what way it didn't work. (Error message? Data incorrect (and if so, how)?

answered Feb 02 '17 at 23:41

Mark Adelsberger

42,148
4
35
52

You say about a join.. would this be easier? – Han Feb 02 '17 at 23:52
Eh... it could be rewritten based on a join. Unless you see a performance problem with one or the other, I'd use whichever seems to you that it more intuitively reflects the intent of the logic. – Mark Adelsberger Feb 03 '17 at 00:30
Yeah, see joins again - and note that LIMIT without ORDER BY is pretty much meaningless. – Strawberry Feb 03 '17 at 16:50

score 0 · Answer 2 · answered Feb 02 '17 at 23:41

0

Perhaps I did not understand the question but is it not as simple as below:

SELECT * FROM products WHERE prodID IN (SELECT prodID FROM keywords WHERE proid<>22)) LIMIT 4;

answered Feb 02 '17 at 23:41

MrMoore

1
1

Defrag · Answer 3 · 2017-02-03T00:03:06.790

I personally don't like nesting subqueries that much, but if you have to go with that, you can achieve your result by adding 'prodID <> 22' just before the LIMIT:

SELECT * FROM products WHERE prodID IN 
  (SELECT prodID FROM keywords WHERE attributeID IN 
    (SELECT attributeID FROM keywords a WHERE prodID = 22)) 
AND prodID <> 22 LIMIT 4;

If you're not bound to that, please notice that you can achieve better performance by using joins:

A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better—a fact that is not specific to MySQL Server alone.

Prior to SQL-92, outer joins did not exist, so subqueries were the only way to do certain things. Today, MySQL Server and many other modern database systems offer a wide range of outer join types

.

You may want to look at these to improve your queries performance: https://dev.mysql.com/doc/refman/5.7/en/rewriting-subqueries.html

MySQL Query to not select the same ID

3 Answers3