Reputation: 826
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
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