Query help needed (on MySQL 4.1.2)

[wrightee]

Sorry for the rubbish post title...

I have two columns, say 'widget_id' & 'category_id'.

select widget_id from tbl where category_id=9 gives me a list of widget_ids 1,2,3..100

My question - is there another way to find the answer to the following query without using a monster 'IN':

select distinct(category_id) from tbl where widget_id IN(1,2,3...100)

In other words and slightly more complex - "You're looking at widgets found in both category 9 AND 10 user, widgets that appear in category 9 & 10 also appear in categories 1,2,3..100".

Hope that makes sense.

Thanks in advance, C

[maximillianos]

You can use a nested select statement, something like this:

select distinct(category_id) from tbl where widget_id IN (select widget_id from tbl_widgets where...)

Not sure if this helps you. But it would simplify the code if you are able to pull them from another table based on some logic/conditions...

[wrightee]

Thanks for the response; sadly 4.1.2 doesn't support nested queries though (I think).