Difficulty checking in MySQL

Question

Difficulty checking in MySQL

Navigation

#1 by (5 votes)
#2 by (4 votes)

3

Firstly I'm starting to mess with mySQL now I'm very lazy on the subject yet

I have a table that is named products_search_items and in this table there are 2 columns product_i" and search_item_id

I needed to, for example, get all product_id that has a search_item_id value of 20 and 14

With this query I did I get all the search_item_id of value 20 and show the product_id with that search_item_id

SELECT id.product_id FROM products_search_items AS id
INNER JOIN products ON products.id = id.product_id 
WHERE search_item_id = 20

For example here on this table

# product_id search_item_id
    26              14
    26              20
    29              29
    29              20
    44              31

this query would return this:

# product_id
    26      
    29

But I need it to return only the product_id that have the search_item_id value of 20 and 14. It must necessarily have two search_item_id . If, for example, there is only one of them, then the product_id can not be returned.

So, in the above dataset would have to return something like this:

# product_id
    26

mysql sql query

asked by anonymous 13.01.2016 / 16:29

2 answers

5

Just use the IN operator in the query:

SELECT psi.product_id FROM products_search_items AS psi
INNER JOIN products ON products.id = psi.product_id 
WHERE search_item_id IN (20,14)

The result:

product_id
26
26
29

I have also changed the alias you gave in the table products_search_items from id to psi , if not confused to query .

Here you can learn more about the IN command.

13.01.2016 / 16:46

Protecting data in memory Aimed from Aimed C

score 4 · Accepted Answer

I believe this is what you need:

select product_id from products_search_items
where search_item_id in (14,20) 
group by product_id
having count(*) = 2

This SQL will return only the product_id that has count (*) = 2, ie only those that have search_item_id = 14 or search_item_id = 20.

In the question example, return only the product_id = 26