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

score 1 · Answer 1 · answered Aug 07 '15 at 13:45

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 Aug 07 '15 at 13:45

Jens

67,715
15
98
113

@Munna Yes as you see – Jens Aug 07 '15 at 13:51
Sorry Its just showing wrong output :( . it just showing pages table page_id column as second table control_id and page_id :( – Munna Babu Aug 07 '15 at 13:56

score 1 · Answer 2 · answered Aug 07 '15 at 13:49

1

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 Aug 07 '15 at 13:49

Arun Krish

2,153
1
10
15

score 1 · Accepted Answer · answered Aug 07 '15 at 13:55

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;

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

3 Answers3