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.