SQL doubt using subquery and IN command

Asked

Viewed 162 times

1

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

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 templates that each user has. So I need to find other users that are in the user_mod table that have the same idmodelo from the so-and-so for each of the 3 so-and-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 that, but it didn’t work. He always considers only the $idmod1. That is, if the user has this model inside his SELECT, it is already listed. I wanted to list only users who necessarily had the 3 models $idmod1,$idmod2,$idmod3 within the outcome of SELECT subquery. Does anyone know if there are any commands for this to work? I am using Mysql database.

Thank you very much!

sql select

  • You are programming with PHP?

  • Yes, but I would need this selection to be done within the SQL pq itself if I first list all users to then loop in PHP and identify those who have the same 3 car models, will run too loop pq are many users (I don’t know if that’s what you were going to propose).

  • One solution I found was to do 3 subquery, each one for a template. ('$idmod1) IN (Select d.IDModelo From user_mod d Where a.IDUser=d.IDUser) AND ($idmod2) IN (Select d.IDModelo From user_mod d Where a.IDUser=d.IDUser) AND ('$idmod3') IN (Select d.IDModelo From user_mod d Where a.IDUser=d.IDUser).... But this will not solve when there are 10 car models at the same time. It will be 10 subquery and a lot of other conditions Where together. It will require a lot of database.

1 answer

0


I’ll give you a concept (as I don’t know your bank), but that’s the idea:

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 the 3 cars selected.

What did I do: I filtered all cars from the car models table, grouped by user, so I only took the ones that add up to >=3, and then pulled from the table users who are.

  • Yeah, I was up all night thinking and I came to the conclusion of using Count. But my solution with Count was still very big. Yours is much simpler and straightforward. Thank you very much!! Ps: I only had to remove DISTINCT to work as desired with the other variables that existed in the final select. But everything else worked perfectly. Thank you very much!

Browser other questions tagged sql select

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