Hi!
I have a problem with a query. I have a given amount of values (id:s) (for example 1,2,6,12,77,78,79,122,123,124), these are related to a table, "pref", and belongs to a specific category, "pref_cat"-table. So, 1,2,6,12 are connected to id 1 in "pref_cat", 77,78,79 to id 2 and 122,123,124 to id 3 in "pref_cat".
Table: "pref"
------------------------------------------------
| id | bigint(20) unsigned | | user_id | bigint(20) unsigned | | category_id | bigint(20) unsigned |
| option_id | bigint(20) unsigned | ------------------------------------------------
Each user have X nr of rows in this table. From the given id:s I want match them with this table in the way that one id (option_id) from the given values in each category_id (ex 1,2 and 3) should be in this table. So, if a user X have id 1, 77, 122 in this table he should be a match. Below I have query that doesn't work, the reason it doesn't work is because it tries to match to the same id in "pref".
SELECT DISTINCT u.id FROM user u INNER JOIN pref p ON p.user_id = u.id AND p.option_id IN ( 1,2,6,12 ) AND p.option_id IN ( 77,78,79) AND p.option_id IN ( 122,123,124 )
What I rather want to do is to match all p.option_id:s for the specific user with the ones in the "IN". For example if a user have the id: 1, 77 AND 122 I want to to a match like this:
SELECT DISTINCT u.id FROM user u INNER JOIN pref p ON p.user_id = u.id AND "if any of (1,77,122) exist IN (1,2,6,12)" AND "if any of (1,77,122) exist IN (77,78,79)" AND "if any of (1,77,122) exist IN (122,123,124 )"
Is this in some way possible to do or does someone have an other solution in mind?
(Maybe I should tell you that I'm using mySQL 4.1)
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
