Kevin Kouketsu
Kevin Kouketsu

Reputation: 826

JOIN with second select result

I've two tables: perfil (profile) and user

On perfil I have the column user_id that means the specified user.

I am trying to take with user.user (that means kevinkouketsu for example) the user.id to use on perfil.user_id

SELECT perfil.*, userId.*, r.Nome as cityName, userId.id as userId 
FROM perfil 
LEFT JOIN (SELECT * FROM user WHERE user='SOMEVALUE') as userId ON perfil.user_id=userId.id 
LEFT JOIN municipio AS r ON perfil.city=r.Codigo

I made this code and results everything on perfil table and alias userId results NULL.

Results example:

array (size=20)   
 'id' => null  
 'user_id' => string '1' (length=1)  
 'conjuge' => string 'Alice Elias Patrício' (length=21)  
 'conjuge_aniver' => string '2018-02-24 20:49:10' (length=19)  
 'state' => string 'SC' (length=2)  
 'city' => string '4217709' (length=7)  
 'perfil_img' => string '' (length=0)  
 'user' => null  
 'senha' => null    
 'email' => null  
 'nome' => null  
 'access' => null  
 'type' => null  
 'empresa_id' => null
 'rede_id' => null  
 'birthday' => null  
 'created' => null  
 'phone' => null  
 'cityName' => string 'Sombrio' (length=7)  
 'userId' => null<

Upvotes: 1

Views: 51

Answers (1)

tyro
tyro

Reputation: 1428

Try this:

SELECT perfil.*, user.*, r.Nome as cityName, user.id as userId 
FROM user 
LEFT JOIN perfil ON perfil.user_id=user.id 
LEFT JOIN municipio AS r ON perfil.city=r.Codigo
WHERE user.user='SOMEVALUE'

Upvotes: 1

Related Questions