CODEWITHSUNDEEP
sqlsql-server
acadia
acadia

Reputation: 2625

Check table with certain values in the other table

I have two tables as shown below

EmpMaster

empid firstName  lastName  lstatus
1       Mark      Rowen      1
2       John      Griffin    1
3       Derick    Rose       1


EmpPers
----------
empPersSeq  empID   perTypeID   PersTypeValue
1             1        1            Good
2             1        2            Fair 
3             1        3            Good 
4             2        1            Fair
5             2        2            Fair
6             2        2            Fair

How do get list of all EmpIds from empMaster that doesnt have perTypeID=3 records in EmpPers Table?

Thanks in advance

Upvotes: 0

Views: 56

Answers (4)

Chopin
Chopin

Reputation: 1452

If I've understood you correctly, you can do:

SELECT e.empid FROM EmpMaster e
WHERE    (SELECT COUNT(*) 
         FROM EmpPers p
         WHERE e.empid = p.empID AND p.empPersSeq = 3) = 0

Upvotes: 0

davesnitty
davesnitty

Reputation: 1850

You can join the 2 tables, aggregate on empid, and use the HAVING clause to choose only the empids where perTypeID is never = 3.

select a.empid
from EmpMaster a
    left join EmpPers b
    on a.empid = b.empid
group by a.empid
HAVING max(case when b.perTypeID = 3 then 1 else 0 end) =0

Upvotes: 0

brian
brian

Reputation: 3695

I believe a NOT EXISTS would do the trick:

SELECT 
em.empid
FROM EmpMaster em
WHERE NOT EXISTS (
    SELECT NULL 
    FROM EmpPers 
    WHERE empID = em.empid AND perTypeID = 3
);

Upvotes: 2

Matt Hamilton
Matt Hamilton

Reputation: 204129

select *
from EmpMaster e
where not exists (
    select *
    from EmpPers p
    where p.empID = e.empID and p.perTypeID = 3
)

Upvotes: 2

Related Questions