CODEWITHSUNDEEP
mysqlsqllogic
DonOfDen
DonOfDen

Reputation: 4108

mysql where condition based on left join field

I am working on a logic where If the LEFT joint field is null then a condition in Where should not be applied. Can some one give me an idea how can It be done with only query.

I tried using IF CASE but its not helping,

Query:

SELECT *
FROM tableA ta
LEFT JOIN tableB tb ON tb.id = ta.id_c
WHERE ta.sales = 'Closed'
  AND tb.deleted=0

When ta.id_c is null, the condition in where AND tb.deleted=0 shouldnt taken in to account.

Why? Because when there is a null value in the linking ID there is no selection of data for the query. Any help? thanks in advance.

Expected result: Select columns of the tables.

Actual Table:

tableA:

id_c    sales  
   1    Closed 
   2    Closed

tableB:

   id  deleted 
   1    0

After Query: Current

id_c    sales   id  deleted 
   1    Closed  1   0

EXPECTED

id_c    sales   id    deleted 
   1    Closed  1       0
   2    Closed  NULL   NULL

Note: I cannot able to edit the LEFT JOIN conditions

Upvotes: 0

Views: 928

Answers (1)

WEBjuju
WEBjuju

Reputation: 6591

Given

create table tableA (
  id_c integer,
  sales varchar(255)
);

create table tableB (
  id integer,
  deleted bool
);

insert into tableA values (1, 'Closed'), (2, 'Closed');
insert into tableB values (1, 0);

SELECT *
FROM tableA ta
LEFT JOIN tableB tb ON tb.id = ta.id_c
WHERE
  (ta.sales = 'Closed' AND tb.deleted = 0) OR
  (ta.sales = 'Closed' AND tb.id is null)

or a little less wordy

WHERE
  (ta.sales = 'Closed') AND (tb.deleted = 0 OR tb.id is null);

results in

id_c    sales   id  deleted 
1   Closed  1   0
2   Closed  NULL    NULL

Upvotes: 2

Related Questions