CODEWITHSUNDEEP
phpmysql
test131
test131

Reputation: 93

My sql getting empty value in table using join table

How to select all data in first table join with second table print null if dont have table

table 1 

|---------------------|------------------|------------------|------------------|
|      id .           |      name        |  success_img     | unsuccess_img
|---------------------|------------------|------------------|------------------|
|          1          |     name1 .      |   success        | unsuccess
|---------------------|------------------|------------------|------------------|  |---------------------|------------------|------------------|------------------|
|          2          |     name2 .      |   success        | unsuccess
|---------------------|------------------|------------------|------------------|

table 2

|---------------------|------------------|------------------|------------------|
|      id .           |  condition_id    |  member_id       | add_by
|---------------------|------------------|------------------|------------------|
|          1          |     1 .   .      |   10             | admin
|---------------------|------------------|------------------|------------------|

I want to out put

table1.id name success_img unsuccess_img member_id add_by

  1    name1     success      unsuccess      10      admin

  2    name2     success      unsuccess      null      null

I try to use 

select * from table1 
left join table2 on table1.id = table2.id
where member_id = 10

this query it's printing out only name 1 which have member_id = 10

Upvotes: 0

Views: 25

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

Move the where condition to the on clause:

SELECT *
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.id = t2.id AND
       t2.member_id = 10;

Having the check on the member_id in the WHERE clause was causing the missing record to be filtered off.

Upvotes: 1

Related Questions