MySQL "IS IN" equivalent?
- by nute
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)?
