How to select full left table and where condition match records from right table?

Question

Please some one tell me how to do this stuff. i have two tables , i need to select entire first table(pages) and from sencond table where user_id = 1

table 1: page

--------------------------------------
  page_id  |  page_url |   details   |  
--------------------------------------
      1    |    xxxx   |   wdredrr   |
      2    |    yyyy   |   rdsacsa   |
      3    |    zzzz   |   rscsacc   |
      4    |    aaaa   |   xdsxsxs   |
      5    |    bbbb   |   drxcraa   |
--------------------------------------

table 2: control

-------------------------------------
control_id |  page_id  |   user_id  |  
-------------------------------------
      1    |      1     |      1    |
      2    |      3     |      1    |
      3    |      4     |      1    |
      4    |      1     |      2    |
      5    |      2     |      2    |
-------------------------------------

and this is what expecting output.

expecting output

--------------------------------------------------------------
  page_id  |  page_url |   details  | control_id |   user_id  |
--------------------------------------------------------------
      1    |    xxxx   |   wdredrr   |      1     |      1    |
      2    |    yyyy   |   rdsacsa   |    null    |    null   |
      3    |    zzzz   |   rscsacc   |      2     |      1    |
      4    |    aaaa   |   xdsxsxs   |    null    |    null   |
      5    |    bbbb   |   drxcraa   |      3     |      1    |
--------------------------------------------------------------

pages JOIN control ON page.page_id = control.page_id WHERE control.user_id = '1'

please someone help to solve this problem. i tried with LEFT JOIN and RIGHT JOIN but i get user_id = 1 matched rows only

Answer 1

Inner join will check if the related data is available with the join condition in the related tables and hence it will filter out unmatched data. You need to use left join and the conditions in the joining clause something as

select 
p.*,
c.control_id,
c.user_id 
from page p left join control c on c.page_id = p.page_id and c.user_id = 1 
order by p.page_id;