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

ahmed salah 3,131 Reputation points
2021-03-28T17:08:54.817+00:00

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 sapre 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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,571 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,121 Reputation points Microsoft Employee
    2021-03-29T02:11:43.957+00:00

    Hi @ahmed salah ,

    Please help double check below:

    82174-ddl.png

    Please also refer below query using LEFT JOIN:

    drop table if exists #categories1,#categories2  
      
    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 #categories2(SpareNo,CategoryId)  
     values  
     (1200,5050),  
     (1200,5090),  
     (1200,5070),  
     (1350,7090),  
     (1350,4020),  
     (1700,8612),  
     (1990,7575),  
     (1990,2050),  
     (7000,4200),  
     (7000,4500)  
      
    select a.*   
    from #categories1 a  
    left join  #categories2 b   
    on a.SpareNo=b.SpareNo and a.CategoryId=b.CategoryId  
    where b.catId is null  
    

    Output:

    catId	SpareNo	CategoryId  
    2	1350	5902  
    3	1700	8070  
    5	7000	2030  
    

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    No comments

2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 68,206 Reputation points Microsoft MVP
    2021-03-28T17:34:43.89+00:00

    Looks like this plain NOT EXISTS query:

    SELECT c1.catId, c1.SpareNo, c1.CategoryId
    FROM   #categories1 c1
    WHERE  NOT EXISTS (SELECT *
                       FROM   #categories2 c2
                       WHERE  c2.CategoryId = c1.CategoryId
                         AND  c2.SpareNo    = c1.SpareNo)
    

  2. Joe Celko 16 Reputation points
    2021-03-30T14:52:56.487+00:00

    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 sapre no and same category on table categories 2

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

    You have a number of fundamental design flaws here

    1) a table must have a key. By definition. This is not an option. What you posted is basically a list in which all columns can be NULL, so this thing can never have a key.

    2) in the correct data model, there is no such thing as a magic universal generic "category"; it has , to be a particular kind of category. This follows from the law of identity, which is the basis of all Western logic. Also look at ISO 11179 standards or any good book on data modeling.

    3) the proprietary "identity" column is not an attribute; is a table property and by definition cannot be a key. Congratulations, you just mimicked a 1950s sequential tape file in SQL instead of getting a table.

    4) categories are by their nature on a nominal scale. You don't do arithmetic on them. Therefore they should not be numeric.

    5) you have two non-tables with the same structure. You might want to read articles by Chris Date on why this is completely wrong. I've referred to in my writings as attribute splitting. I am going to give you the benefit of the doubt and that these two identical non-tables actually represent separate sets of entities, instead of a horrible design that you're trying to repair on the fly.

    CREATE TABLE Foobar_Categories
    (spare_nbr CHAR(4) NOT NULL,
    foobar_cat CHAR(4) NOT NULL,
    data_source INTEGER NOT NULL CHECK(data_source IN (1, 2)),
    PRIMARY KEY (spare_nbr, foobar_cat) --- not a option! );

    INSERT INTO Foobar_Categories
    VALUES
    ('1200', '5050', 1),
    ('1200', 5050, 2),
    ('1200', 5070, 2),
    ('1200', 5090, 2),
    ('1350', '5902', 1),
    ('1350', 4020, 2),
    ('1350', 7090, 2),
    ('1700', '8070', 1),
    ('1700', 8612, 2),
    ('1990', '2050', 1),
    ('1990', 2050, 2),
    ('1990', 7575, 2),
    ('7000', '2030', 1),
    ('7000', 4200, 2),
    ('7000', 4500, 2);

    At no time do you define what these categories mean. Can you imagine a library where the Dewey Decimal Classification codes have no meaning? The nature of a category is that eventually it has to have a meaning.

    >How to get spare_nbr from Foobar_Categories from source =1 where not exist same spare_nbr where source =2? <<

    I have tried to rewrite your specification using a normalized schema. It doesn't work very well :-( when you've got a couple of years experience with RDBMS, you realize that 80 to 90% of the work is done in the DDL and not included in the DML after the fact. You're using identity as a 1960s assembly language cleaner chain! Don't feel bad; a lot of programmers who grew up with filesystems make this error when they try to learn RDBMS. Think of it as learning a foreign language which has a grammar whose rules are completely different.