question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked ErlandSommarskog commented

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

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-generalsql-server-transact-sql
image.png (4.3 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ahmedsalah-1628,

Could you please validate the lastet answers and provide any update? Thanks.

Best regards
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @ahmedsalah-1628,

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.


ddl.png (26.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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)

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thank you for reply
solution above give me wrong result as below :
catId SpareNo CategoryId
1 1200 5050
2 1350 5902
3 1700 8070
4 1990 2050
5 7000 2030

i get wrong result
why display spare no 1990 and 1200
it must not display it exist with same spare no and category on categories2 table
spare no 1200 exist with same category on table categories 2
also spare no 1990 have category 2050 on table categories 2
so i don't need display any spare no have same category on table categories 2
so what i do

0 Votes 0 ·

Not sure what you are talking about. When I ran my query with the sample data you provided (with the correction of the table name that Melissa pointed out), I got the result you asked for. See the complete script in the next post.

If you got wrong result with some other dataset, you need to show us that data.

0 Votes 0 ·
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)
go
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)
0 Votes 0 ·
JoeCelko-6699 avatar image
0 Votes"
JoeCelko-6699 Suspended answered ErlandSommarskog commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Just ignore this Joe Celko-thing. It's just a failed AI experiment that has been let loose.

0 Votes 0 ·