Impossible MySQL query?

[eluser]xarazar[/eluser]
Hi all,
I've just stumbled upon a query I need to form but as ridiculous as it sounds I really can't crack it. For test purposes I simplified my table so now it consists of only 2 fields: id and value with the following entries:
id - value
1 - 2
1 - 3
1 - 4
2 - 2
3 - 3

So if I want to get id's with, say, value 3 selected - no brainer
SELECT * FROM table WHERE val = 3 (returns 1 & 3)

But what I need is to get those id's with two or more values associated with them.
So for instance I need to query the database to return the id which has BOTH 2 and 3 associated with it. In our case it would be id = 1.

I'm sure there must be a simple answer to that but I must be having a senior moment...
Many thanks for any hint.

[eluser]Twisted1919[/eluser]
You should try :

Code:

SELECT * FROM table WHERE `value` IN (2,3);

but your design is bad, because in this case it will return 2 and 3 also, you can however group the result something like :

Code:

SELECT * FROM table WHERE `value` IN (2,3) GROUP BY `value`;

Maybe you can give the full table structure and why doesn't your primary key doesn't auto increment(the id key) ? is there any reason for that ?

[eluser]WanWizard[/eluser]
Try

Code:

SELECT id FROM table GROUP BY id HAVING COUNT( value ) >1

[eluser]xarazar[/eluser]
Thanks for that twisted.
As I mentioned it's a simplified version of the table. In reality it is part of an online shop responsible for association of products with their features, e.g. product 'car' has features: 'fast' and 'red'. So in practice the table columns are: product_id, feature_id. One product can have many features (hence no auto-increment).
What I want to achieve is to allow users to narrow down search results (e.g. show cars that are 'fast' AND 'red').
The trouble is that the list of features is dynamic and new features can be added in the future so I can't incorporate the features within the product table.
Hope it makes sense.

[eluser]xarazar[/eluser]
Thanks WanWizard.
This obviously works but there is a logical error in the result. It returns those id's that have more than 1 value associated with them. What I need is to be able to get those id's with specific values (not value count), e.g.
get id with value = 3 and 4
or
get id with value = 2 and 3

I probably didn't explain the task correctly, but thanks for your suggestion.

[eluser]Twisted1919[/eluser]
So let me get it straight:
you have something like :

Code:

//features table
feature_id     |     name
1              |     fast
2              |     red
3              |     green

//products table
product_id     |     name
1              |     honda
2              |     BMW
3              |     Mercedes-Benz

// features_to_products
feature_id     |     product_id
1              |     1
1              |     2
1              |     3
2              |     1

So in the above example, all the cars are fast but only honda is red, so if you would like to get the results of the fast red cars, you have to do something like :

Code:

SELECT f.*,p.* FROM features f
INNER JOIN features_to_products f2p ON f.feature_id=f2p.feature_id
INNER JOIN products p ON p.product_id=f2p.product_id
WHERE (f.name LIKE "%red%" AND f.name LIKE "%_fast%")
GROUP BY p.product_id ORDER BY p.product_id DESC

Is this what you want ?

L.E: the forum broke the LIKE "

[eluser]xarazar[/eluser]
Thanks twisted. This is perfect. Much appreciated.

[eluser]WanWizard[/eluser]
[quote author="xarazar" date="1290278986"]Thanks WanWizard.
This obviously works but there is a logical error in the result. It returns those id's that have more than 1 value associated with them.[/quote]

You wrote

But what I need is to get those id’s with two or more values associated with them.

so that's what the query produces.