how to get mysql values based on left join condition

Question

job table

1.id
2.status = 'active'
3.name

repair table

1.repair id
2.job_id
3.process = 'yes|no'
4.status  = '1|2'

job table

id   name  status
1    test  active
2    check active

repair table

repair_id     job_id  process  status
1                1      no        2
2                1      no        1
3                1      yes       2
4                2      no        1
5                2      no        2

here i need to show data which ( process != 'yes' and repair_status != 2 ) group by job_id

i need result after query

---------------------------------------------
job_id    name( job.name ) status( job.status )
------------------------------------------------
2            check           active

Welcome to SO. Please see: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Strawberry, Mar 09 '20 at 10:29
Does this answer your question? [SQL JOIN: Just not able to understand them](https://stackoverflow.com/questions/17078952/sql-join-just-not-able-to-understand-them) — P. Šileikis, Mar 09 '20 at 13:27

score 1 · Accepted Answer · answered Mar 09 '20 at 10:33

In order to get the results that you specify, you mean that process is not yes for any row for the job_id. And then that at least one row has a status <> 2. That would be:

select j.job_id, j.name, j.status
from repair r join
     job j
     on r.job_id = r.id
group by j.job_id, j.name, j.status
having max(process) = 'no' and
       min(repair_status) = 1;

score 0 · Answer 2 · answered Mar 09 '20 at 09:55

0

If you want jobs for which no repair exists with process = 'yes' and status = 2, you can use not exists:

select j.*
from jobs j
where not exists (
    select 1 
    from repair r 
    where r.job_id = j.id and r.process = 'yes' and r.status = 2
)

answered Mar 09 '20 at 09:55

GMB

216,147
25
84
135

how to get mysql values based on left join condition

2 Answers2