SQL query to join four tables

Question

User table - user Id, first name, last name
Investment table - investment Id, investment Name
NDA table- investment Id, user Id, NDA signed(boolean)
Permission table - investment Id, user Id, view permission(boolean)

Result I need - First name and Last name of users who has NDA Signed or view permission true/false for a particular invesment Id.

Input - invesment Id

Output - first name, last name, NDA signed (true/false), view permission (true/false)

I got query where output is first name, last name , NDA signed

SELECT u.First_Name, u.Last_Name, n.Nda_Signed 
FROM user u 
JOIN nda n ON u.User_Id = n.User_Id 
JOIN investment i ON i.Investment_Id = n.Investment_Id 
WHERE i.Investment_Id =347

but having problem in joining both nda and permission table.

Answer 1

I would do this as follows:

select u.user_id,
       u.first_name,
       u.last_name,
       sum(case when typ = 'nda' and yesno = 1 then 1 else 0 end) as nda_signed,
       sum(case when typ = 'per' and yesno = 1 then 1 else 0 end) as vw_perm
  from user u
  join (select investment_id, user_id, nda_signed as yesno, 'nda' as typ
          from nda
        union all
        select investment_id, user_id, view_permission, 'per'
          from permission) x
    on u.user_id = x.user_id
where investment_id = 'xyz'
group by u.user_id,
       u.first_name,
       u.last_name

The inline view (x) merges the nda and permission tables and indicates the source on each row via the typ column. The inline view is joined back to the user table on user_id. Then you can conditionally aggregate to find which are nda_signed and view_perm 1/0.