none
How to get missing parts by sql server query relations ? RRS feed

  • Question

  • Problem

    How to get missing parts by sql server query relations ?

    Details

    create table #temp
    (
    companyId  int
    )
    insert into #temp(companyId) values (5)
    create table #temp2
    (
    companyId  int,
    partid    int
    )
    insert into #temp2(companyId,partid) 
    values
    (5,1),
    (5,2),
    (5,3),
    (5,4)
    create table #temp3
    (
    partid    int
    )
    insert into #temp3(partid) 
    values 
    (3),
    (4)

    final result i need to get is parts exist on #temp2 and not exist on #temp3

    final result

    companyId	partid
    5	          1
    5	          2
       

    Wednesday, January 15, 2020 11:50 PM

Answers

  • Hi,

    Other possibility without subquery

    SELECT t2.* from #temp2 t2
    LEFT JOIN #temp3 t3 ON t2.partid=t3.partid
    WHERE t3.partid IS NULL

    Regards

    Thursday, January 16, 2020 8:32 AM

All replies

  • select companyId,partid from #temp2 t2 
    where not exists (select 1 from #temp3 t3 where t2.partid=t3.partid)

    Wednesday, January 15, 2020 11:59 PM
    Moderator
  • Hi engahmedbarbary,

    select * from #temp2 where partid not in(
    select partid from #temp2 where partid in(select * from #temp3))

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, January 16, 2020 1:52 AM
  • Hi,

    Other possibility without subquery

    SELECT t2.* from #temp2 t2
    LEFT JOIN #temp3 t3 ON t2.partid=t3.partid
    WHERE t3.partid IS NULL

    Regards

    Thursday, January 16, 2020 8:32 AM