CODEWITHSUNDEEP
mysqlsql
Get Off My Lawn
Get Off My Lawn

Reputation: 36351

Select rows where all criteria matches from a key value table

I have the following query:

select *
    from user_interests
    join interests using(interest_id)
    where tag in('running', 'biking')
    and user_id != 1;

Note: The in() values are built dynamically using php, so their could be one value or 100 values, each passed in via user browser $_GET.

My user_interests table is pretty simple, as it is just a list of the interest_id and the user_id.

CREATE TABLE `user_interests` (
  `interest_id` INT(10) UNSIGNED NOT NULL,
  `user_id` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`interest_id`, `user_id`)
);

My interests table is a simple table that holds a list of different types of interests (running, biking, etc.)

CREATE TABLE `interests` (
  `interest_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `tag` VARCHAR(50) NOT NULL,
  `category_id` TINYINT(3) UNSIGNED NOT NULL,
  PRIMARY KEY (`interest_id`),
  UNIQUE INDEX `tag` (`tag`)
);

Since each user can have many interests, how can I search the database for users that have all of the interests in the list. For the query above that would be running and biking. My query just gets people who have at least one of the above, how can I make it so it gets users who have all of the queried interests?

So if a user has 5 interests, and I pass in 2 and they have those 2 in their list of 5, their profile should be returned. If they only have 1 of the 2 passed in there profile should not be returned.

Here is a Fiddle: http://sqlfiddle.com/#!9/29ea2

Upvotes: 0

Views: 1101

Answers (1)

Mateo Barahona
Mateo Barahona

Reputation: 1391

I would try that

SELECT *
FROM user_interests
WHERE user_interests.user_id IN (
            SELECT ui.user_id
            FROM user_interests ui
            left join interests i using(interest_id)
            WHERE i.tag IN ($php_array)
                                ^ PHP
            and ui.user_id != 1
            GROUP BY ui.user_id
            HAVING COUNT(i.tag) >= count($php_array)
                                  ^ PHP
)

Fiddle

Upvotes: 1

Related Questions