locked
How to get spare no from categories 1 table where not exist per same spare no on table categories 2? RRS feed

  • Question

  • User696604810 posted

    How to get spare no from categories 1 table where not exist per same spare no on table categories 2?

    so i need to make select statement query select spare no from table categories 1 that have different categories

    on table categories 2 per same spare no

    as example spare no 1350 have category 5902 on table categories1 but on table categories2

    i have same same spare no 1350 but have different categories as 7090 and 4020

    then i select or display this spare no from table categories 1

    as example spare no 1200 have category 5050 on table categories1 but on table categories2

    i have same same spare no 1200 but have same categories as 5050 on table categories 2

    so i don't need it or don't need to display it because it exist same spare no and same category on table categories 2

    so How to make select query give me expected result below ?

    create table #categories1
     (
     catId int identity(1,1),
     SpareNo int,
     CategoryId int,
     )
     insert into #categories1(SpareNo,CategoryId)
     values
     (1200,5050),
     (1350,5902),
     (1700,8070),
     (1990,2050),
     (7000,2030)
        
     create table #categories2
     (
     catId int identity(1,1),
     SpareNo int,
     CategoryId int,
     )
     insert into #categories(SpareNo,CategoryId)
     values
     (1200,5050),
     (1200,5090),
     (1200,5070),
     (1350,7090),
     (1350,4020),
     (1700,8612),
     (1990,7575),
     (1990,2050),
     (7000,4200),
     (7000,4500)
    
    
    expected result :
    catId SpareNo CategoryId
    2 1350 5902
    3 1700 8070
    5 7000 2030

    82167-image.png

    Sunday, March 28, 2021 5:16 PM

All replies

  • User-939850651 posted

    Hi ahmedbarbary,

    According to your description, Table 1 may contain records that are not in Table 2. If you want to query them, I think you only need to filter out the unique records from Table 2 to achieve your requirements (use  CategoryId ).

    Something like this:

    select c1.catId,c1.SpareNo,c1.CategoryId from #categories1 c1
    where c1.CategoryId not in 
           (select CategoryId from #categories2 where #categories2.SpareNo = c1.SpareNo)

    Hope this can help.

    Best regards,

    Xudong Peng

    Monday, March 29, 2021 7:32 AM
  • User-1716253493 posted
    SELECT categories1.catId, categories1.SpareNo, categories1.CategoryId
    FROM categories1 LEFT OUTER JOIN
    categories2 ON categories1.SpareNo = categories2.SpareNo AND categories1.CategoryId = categories2.CategoryId
    WHERE (categories2.SpareNo IS NULL)

    Monday, March 29, 2021 2:57 PM