Displaying null results for mysql related tables

Question

I need to create a relational query that returns records containing data from both the PERMISSAO table and the USUARIOS table. ALL PERMISSIONS records must be displayed. When there are no USER records, NULL must be displayed. The PERM_USU is used to relate the USUARIOS and PERMISSAO tables (in multi-schema for multiple). There is a condition, where PERM_USU.USUARIO_ID = 34

permission

• id (int)
• permission (varchar)

users

• id (int)
• user (varchar)

perm_usu

• permission_id (int)
• user_id (int)

I tried the query below, but it returns only the records that exist in Table 1 and Table 3.

select PERMISSAO.*, USUARIOS.* from PERMISSAO
left join PERM_USU on PERMISSAO.ID = PERM_USU.PERMISSAO_ID
left join USUARIOS on USUARIOS.ID = PERM_USU.USUARIO_ID
where PERM_USU.USUARIO_ID = 34

Thanks in advance for the help

Answer 1

You can use OUTER APPLY (with SQL Server) or a sub-query on Join (I think in other banks), something like that:

Using OUTER APPLY:

select
    p.*,
    oau.*
from
    PERMISSAO p
    outer apply(select
                    u.*
                from
                    USUARIOS u
                    inner join PERM_USU pu on (pu.usuario_id = u.id and pu.permissao_id = p.id)
                where
                    u.id = 34) oau

Using subconsulta in the Join:

select
    p.*,
    sq.id,
    sq.usuario
from
    PERMISSAO p
    left join ( select
                    pu.permissao_id,
                    u.*
                from
                    USUARIOS u
                    inner join PERM_USU pu on (pu.usuario_id = u.id)
                where
                    u.id = 34) sq on (sq.permissao_id = p.id)