sql - Could this be done more efficiently -

i have table 2 columns (p_id, id_type) p_id can have multiple types. need find p_ids not have specific type.

p_id                    id_type -----------          ------------- 12456                      6 12456                      7  56897                      10 25686                      9 25686                      22 25686                      7 56897                      22 

this query used wondering if there more efficient way this.

select p_id  #temp1 table2 id_type = 6  select   distinct table2.p_id  ,table1.name ,table1.type   table2 left join table1 on table2.p_id = table1.p_id  table2.p_id not in   (select p_id #temp1) , type = 'xyz' 

expected outcome should p_ids not have id_type = 6.

p_id         name          type 56897        smith         physician 25686        jones         physician 

assuming i'm understanding question correctly, you're trying select p_id rows don't have corresponding p_id rows specific type.

if so, there couple of ways this. 1 use not in:

select * yourtable p_id not in (   select p_id   yourtable   id_type = 6) 

• sql fiddle demo

---

using not exists:

select * yourtable t not exists (   select 1   yourtable t2   t.p_id = t2.p_id ,          t2.id_type = 6) 

• more fiddle

---

you use outer join achieve same result.

if want specific p_id's, need add distinct. it's not clear you're expected output should be.