Can I Write this Query without a sub SELECT? 1

[jeff5311]

i'm trying to construct what i believe would be a fairly simple query against 1 table with just 3 columns. however, with my limited sql knowledge, it feels like i'm going in circles. can any mysql gurus please help?

my table is called "categories" and has the following fields and example data:

product_id category_name category_value
---------- ------------- --------------
widget1 type square
widget1 item engineering
widget1 color black
widget2 type round
widget2 color red
widget2 item engineering
widget3 item chemical
widget4 item chemical
widget4 color red

each product can be assigned 1 to n categories and this table will hold the relationships (may not be a de-normalized table, but for my use, it seemed the best design). for example, widget1 is a black, square, engineering widget, and widget4 is a red, chemical widget.

ok, all i want to be able to do is create a list of all available category_values given a category_value selection. For example, if "red" has been selected, show the category_values of "round," "engineering," and "chemical," since the "red" widgets (widget2 and widget4) have been assigned the additional category values of "round," "engineering," and "chemical." Does this make sense?

i believe this can be done using SUB-SELECTS, but since i don't have mysql 4.1, sub-selects are unfortunately not an option.

i know i have to missing something simple here, right? can anyone help?

thanks in advance for your input!

 

[r937]

do you want all the values that any widget with a red value has, or do you want only those values that all widgets with a red value have?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[jeff5311]

good question! sorry i wasn't clear to start. i want only values that all widgets with a red value have (i think!).

another example from the data would be: if i selected "engineering," i would want to return "black," "red," "square," and "round," since widget1 and widget2 have all of those values (in addition to "engineering").

does this make sense?

thanks again!

 

[ericbrunson]

How about something like:

select a.category_value 
from engineering as a
join engineering as b using ( product_id )
where b.category_value = 'engineering'

P.S.
Check out my join notation, r937. I figure, if you say it's better, there's probably something to it.

 

[jeff5311]

thanks for your help!

if the table is named "categories," do you mean:

select a.category_value
from categories as a
join categories as b using ( product_id )
where b.category_value = 'engineering'

if so, i'll try that and report back.

thanks again!

 

[jeff5311]

ericbrunson,

your SQL worked great! you are a lifesaver!

thanks so much for your help.

jeff