CODEWITHSUNDEEP
mysqljoinleft-joinouter-joinright-join
Munna Babu
Munna Babu

Reputation: 5756

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

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

Upvotes: 0

Views: 1199

Answers (3)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

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;

Upvotes: 1

Arun Krish
Arun Krish

Reputation: 2153

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)

Upvotes: 1

Jens
Jens

Reputation: 69440

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'

Upvotes: 1

Related Questions