Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL "IS IN" equivalent?

A while ago I worked on a MS-SQL project and I remember a "IS IN" thing. I tried it on a MySQL project and it did not work.

Is there an equivalent? Workaround?

Here is the full query I am trying to run:

SELECT *
FROM product_product, product_viewhistory, product_xref
WHERE 
(
(product_viewhistory.productId = product_xref.product_id_1 AND product_xref.product_id_2 = product_product.id)
OR 
(product_viewhistory.productId = product_xref.product_id_2 AND product_xref.product_id_1 = product_product.id)
)
AND product_product.id IS IN 
    (SELECT DISTINCT pvh.productId
    FROM product_viewhistory AS pvh
    WHERE pvh.cookieId = :cookieId
    ORDER BY pvh.viewTime DESC
    LIMIT 10)
AND product_viewhistory.cookieId = :cookieId
AND product_product.outofstock='N'
ORDER BY product_xref.hits DESC
LIMIT 10

It's pretty big ... but the part I am interested in is:

AND product_product.id IS IN 
    (SELECT DISTINCT pvh.productId
    FROM product_viewhistory AS pvh
    WHERE pvh.cookieId = :cookieId
    ORDER BY pvh.viewTime DESC
    LIMIT 10)

Which basically says I want the products to be in the "top 10" of that sub-query.

How would you achieve that with MySQL (while trying to be efficient)?

like image 620
Nathan H Avatar asked Apr 16 '10 00:04

Nathan H


People also ask

What is equivalent of minus in MySQL?

MySQL Does not supports MINUS or EXCEPT ,You can use NOT EXISTS , NULL or NOT IN .

What is equal in MySQL?

MySQL equal operator performs an equality comparison. Syntax: = MySQL Version: 5.6. Example: MySQL equal operator. The following MySQL statement checks if 1 is equal to 1, if 1 is equal to 2, if NULL is equal to NULL, if NULL is equal to 3 and if 3 is equal to NULL.

IS NULL equivalent in MySQL?

In MySQL, 0 or NULL means false and anything else means true. The default truth value from a boolean operation is 1 .

What is Find_in_set in MySQL?

The FIND_IN_SET() function returns the position of a string within a list of strings.


2 Answers

You're looking for IN:

AND product_product.id IN 
    (SELECT DISTINCT pvh.productId
    FROM product_viewhistory AS pvh
    WHERE pvh.cookieId = :cookieId
    ORDER BY pvh.viewTime DESC
    LIMIT 10)
like image 114
davidtbernal Avatar answered Sep 25 '22 13:09

davidtbernal


Try IN (no "is")

like image 22
nickf Avatar answered Sep 23 '22 13:09

nickf