mysql where condition based on left join field

Question

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

What results are you not getting that you expect to get, or what results are you getting that you don't want to get? — CLAbeel, Dec 02 '16 at 12:39

WEBjuju · Accepted Answer · 2016-12-02T13:05:12.287

2

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

edited Dec 02 '16 at 13:05

answered Dec 02 '16 at 12:45

WEBjuju

5,797
4
27
36

oh, i just saw that you can't edit the LEFT JOIN conditions. i don't understand that; please explain more clearly why and what else you can't edit. – WEBjuju Dec 02 '16 at 12:47
I dont want NULL, What i mean is in some record the ta.id_c is null, so the entire record is not displaying because the tb.deleted=0.. – DonOfDen Dec 02 '16 at 12:47
as you see how to give a data example in this solution, please clarify the data by giving the table structure and the example data, also including expected result. – WEBjuju Dec 02 '16 at 12:48
I cant edit the JOINT, But i can change there where condition so if the ta.id_c is not null I can add the condtion 'AND tb.deleted=0'. – DonOfDen Dec 02 '16 at 12:48
see my **UPDATE** pls – WEBjuju Dec 02 '16 at 12:58
1

Solved the question ;) – WEBjuju Dec 02 '16 at 13:05
Yup! Its working and wonderful solution.! Do you think this can be done in anyother way? different solutions ? – DonOfDen Dec 02 '16 at 13:07
1

Maybe, I could not help but think [Foreign Keys](http://stackoverflow.com/questions/757181/basics-of-foreign-keys-in-mysql) would prevent the [orphan sql rows](http://stackoverflow.com/questions/6564034/deleting-orphans-from-a-table), but that requires a premeditated, design to the application and can't be done as a work around (not without some cleanup). – WEBjuju Dec 02 '16 at 13:12

mysql where condition based on left join field

1 Answers1