CODEWITHSUNDEEP
phpmysql
John W
John W

Reputation: 161

MYSQL SELECT JOIN with condition

I'm working on a tricky query and just don't understand how to approach it since neither JOIN gives me desirable result.

I have two tables:

Table1:
id
value

Tabel2:
id
table1_id
parameter (1,0)
value

I need to select everything from Table_1, but if there is a row in Table2 with table1_id = table1.id and parameter = 1, I want to include table2.value in the outcome. Note, that there can be multiple rows with table1_id = table1.id in Table2, but only one with parameter=1.

So, what I'm looking to get as a a result

table1.id | table1.value | table2.parameter |table2.value
    1     |      v1      |                  |      
    2     |      v1      |         1        |      v2
    3     |      v1      |                  |      
    4     |      v1      |         1        |      v2

Can someone help me with a query. Thank you for your time.

Upvotes: 2

Views: 52

Answers (2)

Halayem Anis
Halayem Anis

Reputation: 7785

SELECT *
FROM
  Table1 LEFT JOIN Table2
  ON (Table1.id = Table2.table1_id AND Table2.parameter = 1)
;

Upvotes: 2

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You can use left join and case when for showing the table2 value

select
t1.id,
t1.value,
t2.parameter,
case when t2.table_id is not null and t2.parameter = 1 then t2.value else null end as table2_value
from table1 t1
left join table2 t2 on t2.table1_id = t1.id

Upvotes: 1

Related Questions