SQL query with subquery and IN command

Question

SQL query with subquery and IN command

Navigation

#1 by (1 votes)

2

I need to do the following. So-and-so has several models of cars. I want to list other users who also have 3 specific models of car that so and so.

What I did:

$iduser = id do fulano
$mod1 = modelo de carro 1 de fulano 
$mod2 = modelo de carro 2 de fulano 
$mod3 = modelo de carro 3 de fulano

I have a table ( user ) for users and a table ( user_mod ) for the models that each user has. So I need to find other users who are in the user_mod table who have the same idmodelo of so and so for each of 3 so-car models.

 SELECT a.* 
 FROM users a 
 WHERE a.IDUser!=$iduser AND ('$idmod1,$idmod2,$idmod3') 
 IN (Select d.IDModelo From user_mod d Where a.IDUser=d.IDUser)

I tried this, but it did not work. It always considers only $idmod1 . That is, if the user has this template within their SELECT , it is already listed. I wanted to list only users who would necessarily have the 3 templates $idmod1,$idmod2,$idmod3 within the result of SELECT of the subquery. Does anyone know if there is any command for this to work? I'm using MySQL database.

Thank you!

sql select

asked by anonymous 02.10.2017 / 08:05

1 answer

ERROR when creating unique constraint with two fields in Laravel request? How to model n-to-n relation correctly? Women also have friendship with men and not only men have friendship with women

score 1 · Accepted Answer

I'll give you a concept (as I do not know your bank), but the idea is this:

SELECT * FROM users
WHERE iduser IN (
SELECT DISTINCT(iduser)
FROM user_mod
WHERE idmodelo IN ('$idmod1','$idmod2','$idmod3')
GROUP BY iduser
HAVING COUNT(*) >= 3 )

This will bring all users who have all 3 cars selected.

What I did: I filtered all cars from the table of car models, grouped by user, so I just picked the ones that gave > = 3, and then I pulled the users table who they are.