Or:
SELECT DISTINCT T1.item_number
FROM Attributes t1, Attributes t2, Attributes t3
WHERE
t1.item_number=t2.item_number
AND t2.item_number=t3.item_number
AND t1.attribute='sport' AND t1.value='football'
AND t2.attribute='league' AND t2.value='nfl'
AND t3.attribute='typeb' AND t3.value='jersey';
However, I agree that the table layout should be redone. If you know that you
are always going to have a sport, league, and typeb associated with an
item_number, you should at least make them columns in a main table. Doing the
self-join as above is general but also CPU expensive. I do not know about the
limitations of MySQL but do not attempt a 6-level self join on a large table in
Informix unless you are planning on watching a movie while it runs.
--Bill
Jason Clark wrote:
> A book I've found very usefull is 'MySQL and mSQL' by O'Reilly
>
> You could use a query like this, but...
>
> "select distinct item_number from Attributes where attribute like 'football'
> and value like 'nfl' "
>
> You will be happier if you rethink your layout and create multiple tables.
> Give them unique identifiers (to link them) and your job will be much
> easier. I can give you some suggestions if you like.
>
> ^ ^
> . .
> >( O )<
> M
> ~ Seien Sie eins mit dem Rad ~
> - Pruf-Gerbil -
>
> -----Original Message-----
> From: Chris Haupt [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, September 20, 2001 4:42 PM
> To: [EMAIL PROTECTED]
> Subject: Query to return products given matches on attributes
>
> Hello,
>
> I have a question about the best way to pose this question:
>
> We have a table named Attributes:
> +-------------+------------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +-------------+------------------+------+-----+---------+----------------+
> | item_number | varchar(50) | YES | | NULL | |
> | attribute | varchar(20) | YES | | NULL | |
> | value | varchar(30) | YES | | NULL | |
> +-------------+------------------+------+-----+---------+----------------+
>
> Sample values for an item from Attributes
> +-------------+--------------+--------------------------------+
> | item_number | attribute | value |
> +-------------+--------------+--------------------------------+
> | OGI-02035 | Price | LT_75 |
> | OGI-02035 | Sport | Football |
> | OGI-02035 | League | NFL |
> | OGI-02035 | TypeB | Jersey |
> +-------------+--------------+--------------------------------+
>
> An example query I'm trying to figure out is:
> Return all item_numbers where sport="football", league="nfl" and
> typeb="jersey"
>
> Any help would be appreciated. Also any pointers to good books that
> people have found that help them solve such problems.
>
> Thanks,
>
> Chris
> ============ O N L I N E S P O R T S =============
> Chris Haupt [EMAIL PROTECTED]
> Online Sports http://www.onlinesports.com
> Phone: 760-839-9363 Fax: 760-839-9370
> Directory of Thousands of Sports Items Available Online Today!
> ==== P R O D U C T S & S E R V I C E S ====
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--
Bill Adams
TriQuint Semiconductor
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
