Displaying null results for mysql related tables

Navigation
1

I need to create a relational query that returns records containing data from both the PERMISSION table and the USERS table. ALL PERMIT records must be displayed. When no records exist in the USER, NULL must be displayed. PERM_USU is used to relate the USERS and PERMISSION tables (in the multi-to-many schema). There is a condition where PERM_USU.USUARIO_ID = 34

Permission

  • id (int)
  • permission (varchar)

users

  • id (int)
  • user (varchar)

perm_usu

  • permissao_id (int)
  • user_id (int)

I tried the query below, but it returns only the records that exist in both table1 and table3. 

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

    
asked by anonymous 18.02.2015 / 16:10

1 answer

1

You can use OUTER APPLY

in> (with SQL Server) or a subquery in join (I think in other banks), something like this:

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 subquery in 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)
    
18.02.2015 / 17:09
.txt File Handling How to keep left-aligned icon when resizing page?