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

Munna Source

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

mysqljoinleft-joinouter-joinright-join

Answers

answered 3 years ago Jens #1

Put the your where clause in the on clause and you should get the expected result:

select * from pages JOIN control ON page.page_id = control.page_id AND control.user_id = '1'

answered 3 years ago Arun Krish #2

Try this

SELECT p.page_id, p.page_url, p.details, IF(c.user_id=1,c.control_id,NULL), IF(c.user_id=1,c.user_id,NULL) FROM page p LEFT JOIN control c ON(c.page_id=p.page_id)

answered 3 years ago Abhik Chakraborty #3

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;

comments powered by Disqus