Selecting several types

[Danny]

Hi all,

So I have these tables of items and item-types, and the items can be
of several types:

Items:
ID ; Name
1 ; Item1
2 ; Item2

ItemType:
ID ; Type
100 ; Type1
200 ; Type2
300 ; Type3

Now, to link these 2 I have a "linking" table:

LinkingTable:
ItemID ; ItemTypeID
1 ; 100
1 ; 200
2 ; 100
2 ; 300

Now I want to select all the items of type 100 or 200...

How can I do that without getting a duplicate row of item 1??

Thanks in advance,
Danny

[Plamen Ratchev]

Re: Selecting several types

You can use DISTINCT:

SELECT DISTINCT I.item_id, I.item_name
FROM Items AS I
JOIN ItemTypes AS T
ON I.item_id = T.item_id
WHERE T.item_type_id IN (100, 200);

HTH,

Plamen Ratchev

http://www.SQLStudio.com

[--CELKO--]

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_descr iption 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 ItemClassificat ions
(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.

[Erland Sommarskog]

Re: Selecting several types

Danny (adler.danny@gm ail.com) writes:

So I have these tables of items and item-types, and the items can be
of several types:
>
Items:
ID ; Name
1 ; Item1
2 ; Item2
>
ItemType:
ID ; Type
100 ; Type1
200 ; Type2
300 ; Type3
>
Now, to link these 2 I have a "linking" table:
>
LinkingTable:
ItemID ; ItemTypeID
1 ; 100
1 ; 200
2 ; 100
2 ; 300
>
Now I want to select all the items of type 100 or 200...
>
How can I do that without getting a duplicate row of item 1??

Another solution is to use EXISTS:

SELECT i.item_id, i.item_name
FROM items i
WHERE EXISTS (SELECT *
FROM linkingtable l
WHERE i.item_id = l.item_id
AND l.item_type_id IN (100, 200))



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Ed Murphy]

Re: Selecting several types

--CELKO-- wrote:

CREATE TABLE ItemClassificat ions
(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);

s/Inventory/ItemClassificat ions/

This will work if all you need is item_id. If you need some data from
the Inventory table as well, then use Plamen's or Erland's approach.