Difficulty to query in Mysql

Asked

Viewed 78 times

3

First I’m starting now to mess with mysql I’m very lay in the subject yet

I have a table called products_search_items and this table has 2 columns a product_i" and the search_item_id

I needed to take all the product_id who has a search_item_id with a value of 20 and 14

with this query I made I can get all the search_item_id value 20 and show 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 it to me :

# product_id
    26      
    29

but I need her to return only the product_id who have the search_item_id of value 20 and 14. You need to have both search_item_id. If, for example, there is only one of them, then product_id cannot be returned.

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

# product_id
    26
mysql sql query

  • What I didn’t understand was the following: If you want to get the product_id from the search_item_id they are (20,14), this implies returning the following product_id: (26,29). Because you deleted product_id 29 from the expected result?

  • então eu queria que retornasse só os product_id que tenha o search_item_id (20,14) o product_id 29 não tem um search_item_id 14, eu queria que retornasse somente o product_id que tivesse o search_item_id(20,14), tipo tem 2 product_id 26, um com search_item_id 20 e outro com 14, i wanted to return only the product_id that had these 2 search_item_id, I do not know if it is possible, and I am also kind of terrible to explain rs,

  • I got it. You only want to get the product_id that necessarily has the search_item_id 14 and 20.

2 answers

5

Just use the operator IN 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 obtained:

product_id
26
26
29

I also changed the Alias you gave in the table products_search_items of id for psi, if you’re not confused query.

Here you can learn more about the command IN.

4


I believe that’s 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 only return product_id that has Count(*) = 2, that is, only those that have search_item_id = 14 or search_item_id = 20.

In the question example, you will return only product_id = 26

Browser other questions tagged mysql sql query

You are not signed in. Login or sign up in order to post.