LEFT JOIN to return only first row-CodePudding

I have been trying with some solutions here on stackoverflow but cant get this to work

I am making a join with two tables, tab_usuarios (users) and tab_enderecos (addrress)

tab_usuarios structure:

id_usuario	nome	usuario
1	Administrador	admin
2	Novo Usuário	teste
3	Joao Silva	jao

tab_enderecos structure:

id_endereco	id_usuario	cidade	uf
2	1	cidade	SP
20	2	Lorena	SP
22	2	Lorena	SP
24	3	Campinas	SP
28	4	Lorena	SP

I have this simple query which brings me the following result:

Select
    u.id_usuario,
    u.usuario,
    u.nome,
    e.id_endereco,
    e.cidade,
    e.uf
From
    tab_usuarios u Left Join
    tab_enderecos e On u.id_usuario = e.id_usuario

id_usuario	usuario	nome	id_endereco	cidade	uf
1	admin	Administrador	2	cidade	SP
2	user 2	Novo Usuário	22	Lorena	SP
2	user 2	Novo Usuário	20	Lorena	SP
3	jao	Joao Silva	24	Campinas	SP
4	teste	fabio	28	Lorena	SP

So, what I want is, for example, for id_usuario = 2, I only want to bring the id_endereco = 20, which is the first address that have been inserted on the database.

I tried with min and a couple others but I am for 1h:30m trying so decided to post.

Thanks for any help.

CodePudding user response：

My answer will assume that first query is correct

 Select
        u.id_usuario,
        u.usuario,
        u.nome,
        e.id_endereco,
        e.cidade,
        e.uf
    From
        tab_usuarios u Left Join
        tab_enderecos e On u.id_usuario = e.id_usuario
        order by u.id_usuario desc limit 1

Hey You might wanna look at the id's I used an see if they work for you

CodePudding user response：

This should do it, assuming you have MySql 8.0 and not some ancient 5.x version:

SELECT * 
FROM (
    SELECT  u.id_usuario, u.usuario, u.nome, e.id_endereco, e.cidade, e.uf,
        row_number() over (partition by u.id_usuario order by e.id_endereco) rn
    FROM tab_usuarios u 
    LEFT JOIN tab_enderecos e On u.id_usuario = e.id_usuario
) t
WHERE rn = 1

CodePudding user response：

I have not tested but the following should work

Select
    u.id_usuario,
    u.usuario,
    u.nome,
    e.id_endereco,
    e.cidade,
    e.uf
From
    tab_usuarios u Left Join
    tab_enderecos e On u.id_usuario = e.id_usuario
Order by e.id_endereco asc
Limit 1;