CODEWITHSUNDEEP
mysqlsqlleft-join
awan
awan

Reputation: 27

SQL LEFT JOIN EXCLUDE TWO RECORDS FROM SECOND TABLE

I am trying LEFT join and also wants to exclude records from second table, if status=Yes. But when I use LEFT join, it gives all the records from table1 without excluding the records from table2 whose status is 'Yes'.

Table1
--------------------------------
id  date
--------------------------------
1   01/09/2020
2   02/09/2020
3   03/09/2020
4   04/09/2020
5   05/09/2020


Table2
--------------------------------
id  date          status
--------------------------------
1   01/09/2020    Yes
2   02/09/2020    Yes
3   03/09/2020

Desired result

--------------------------------
id  date
--------------------------------
3   03/09/2020   
4   04/09/2020   
5   05/09/2020

Excludes id 1,2 because table2 has status="yes".

I know my SQL syntax is wrong. Please advise the correct syntax:

SELECT table1.id,table1.date
FROM table1
LEFT JOIN (select table2.id, table2.date from table2 where status!='Yes') as table2
ON table1.id=table2.id and table1.date = table2.date

Upvotes: 1

Views: 2561

Answers (6)

lamboktulus1379
lamboktulus1379

Reputation: 370

Try this

SELECT t1.* 
FROM Table1 t1 
LEFT JOIN Table2 t2 ON t2.id = t1.id 
WHERE t2.status != 'Yes' 
GROUP BY t1.id;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If you want to filter records, then use an inner join, not an outer join:

SELECT t1.id, t1.date
FROM table1 t1 JOIN
     table2 t2
     ON t1.id = t2.id and t1.date = t2.date
WHERE t2.status <> 'Yes';

EDIT:

Based on your comment, I think NOT EXISTS is the best approach:

select t1.*
from table1 t1
where not exists (select 1
                  from table2 t2
                  where t1.id = t2.id and t1.date = t2.date and
                        t2.status = 'Yes'
                 );

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562230

I would write the solution this way:

SELECT Table1.id, Table1.date
FROM Table1 LEFT OUTER JOIN Table2
  ON Table1.id = Table2.id AND Table2.status = 'Yes'
WHERE Table2.id IS NULL

As you know, a left outer join returns NULL for rows of the right table if there is no match. If the match condition includes status='Yes' then rows with other status values are not a match.

Then the WHERE clause restricts results to rows of Table1 that had no match.

Upvotes: 1

user2560539
user2560539

Reputation:

An alternative query is the following (since i can't really understand why it is needed to join on id also):

SELECT `t1`.`id`,`t1`.`date` FROM `Table1` `t1` 
LEFT JOIN `Table2` `t2` ON `t1`.`date`=`t2`.`date`
WHERE `t2`.`status` <> 'Yes' OR `t2`.`date` IS NULL;

Here is a relevant SQLFiddle

Upvotes: 0

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

You can solve the problem by use NOT EXISTS condition like:

SELECT Table1.*
FROM Table1
WHERE NOT EXISTS (
    SELECT id FROM Table2 WHERE Table2.id = Table1.id AND Table2.status = 'Yes'
);

Upvotes: 1

forpas
forpas

Reputation: 164064

You need to set the correct conditions in the WHERE clause:

SELECT t1.*
FROM Table1 t1 LEFT JOIN Table2 t2
ON t2.id = t1.id AND t2.date = t1.date
WHERE COALESCE(t2.status, '') <> 'Yes'

or:

SELECT t1.*
FROM Table1 t1 LEFT JOIN Table2 t2
ON t2.id = t1.id AND t2.date = t1.date
WHERE t2.status IS NULL OR t2.status <> 'Yes'

See the demo.
Results:

> id | date      
> -: | :---------
>  3 | 03/09/2020
>  4 | 04/09/2020
>  5 | 05/09/2020

Upvotes: 2

Related Questions