>> So I have these tables of items and item-types, and the items can be of
several types: <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Sample data is also a good idea,
along with clear specifications. It is very hard to debug code when
you do not let us see it. If you want to learn how to ask a question
on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html
Here is a guess at what you meant to post:
CREATE TABLE Inventory -- collective noun for a set name
(item_id CHAR(15) NOT NULL PRIMARY KEY, --let's use GTIN
item_name VARCHAR(20) NOT NULL);
CREATE TABLE ItemTypes -- needs a better name; blood type? tarriff
type?
(item_type INTEGER NOT NULL PRIMARY KEY,
item_type_description VARCHAR(50) NOT NULL);
You do know that there is no such thing as a "type_id" -- an attribute
can be one or the other but not both.
>> Now, to link these two tables I have a "linking" table: <<
That is a term from CODASYL databases; we have relations in SQL. They
usually have a proper name, like "Marriages" or "JobAssignments",
etc. I will make a guess at what you meant and add minimal DRI.
CREATE TABLE ItemClassifications
(item_id CHAR(15) NOT NULL
REFERENCES Inventory (item_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
item_type INTEGER NOT NULL
REFERENCES ItemTypes(item_type)
ON UPDATE CASCADE,
PRIMARY KEY (item_id, item_type));
>> Now I want to select all the items of type 100 or 200...<<
SELECT DISTINCT item_id
FROM Inventory
WHERE item_type IN (100, 200);
You need to Google "Relational Division" for more genral solutions to
this kind of query.
|
