CODEWITHSUNDEEP
mysqlmysql-5.7
Vanjith
Vanjith

Reputation: 540

How to form a join query in mysql to filter records based on some status condition

Consider I have table t1

id Another header
1 row1
2 row2
3 row3
4 row4

and another table t2

id t1_id status
1 1 PG
2 2 S
3 1 CG
4 1 S
5 3 CG

t1 has one2many relationship with t2.

The t1_id has multiple entries in t2 table because t2's status sequence goes on like PG > CG >S.

So it can have separate entry for each status it passes through.

Once it reaches status S we should not select that t1_id even if it has any of the previous statuses like PG or CG

I want to do left join like t1 left join t2 based on t1 id And another condition like t2's status should not have S.

So the expected result should be

t1_id status
3 CG
4 null

How can I achieve this ? Thanks in advance

Upvotes: 0

Views: 76

Answers (1)

Rahul Biswas
Rahul Biswas

Reputation: 3467

First retrieve t1_id where status 'S' exists and ignore those t1_id from query if any other status info for that t1_id is existed in the table.

-- MySQL (v5.7)

SELECT t.id t1_id, p.status
FROM t1 t
LEFT JOIN t2 p
       ON t.id = p.t1_id
WHERE NOT EXISTS (SELECT 1
                  FROM t2
                  WHERE t1_id = t.id AND status = 'S');

Please check from url https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=2a38e8a8696b68c15f8e95a28f244c86

Upvotes: 1

Related Questions