Re: Selecting several types

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

Relevant Pages

  • Re: object model to table design mapping problem
    ... the INCITS H2 Database Standards Committee(nee ANSI X3H2 ... NOT NULL PRIMARY KEY, ... REFERENCES Courses, ... ON UPDATE CASCADE ...
    (microsoft.public.sqlserver.programming)
  • Re: String Search
    ... Sample data is also a good idea, ... (proj_nbr INTEGER NOT NULL PRIMARY KEY, ... ON UPDATE CASCADE ... REFERENCES Projects ...
    (microsoft.public.sqlserver.programming)
  • Re: Design for Category Feature
    ... surname VARCHAR NOT NULL, ... REFERENCES ContactCategories ... ON UPDATE CASCADE ... PRIMARY KEY ); ...
    (comp.databases)
  • Re: Cascading update on primary key
    ... primary key depends on one of the 2 secondary tables. ... (mom CHAR(5) ... REFERENCES Mothers ... ON UPDATE CASCADE, ...
    (microsoft.public.sqlserver.programming)
  • Re: How do I discover a tables primary key?
    ... sp_helpconstraints system procedure in isql the result looks like ... an index once I have an entry in sysindexes. ... -- Number of references made by this table: ... from within my program I only get back one row, the PRIMARY KEY ...
    (comp.databases.sybase)