CODEWITHSUNDEEP
mysqlcountexact-match
mtoninelli
mtoninelli

Reputation: 714

MySql - IN operator (with exact values)

Table "argument_user"

+-------------+----------+
| argument    | user_id  |
+-------------+----------+
| bike        | 1        |
| flower      | 2        |
| web         | 1        |
| web         | 2        |
| nature      | 1        |
| nature      | 2        |
| nature      | 3        |
+-------------+----------+

I want get ONLY the arguments in common with user 1 and 2 >> "web" (without "nature")

+-------------+
| web         |
+-------------+

Now I'm using this query, but it's wrong because it returns me "web" and also "house". 

select argument from argument_user 
where user_id in ('1','2') 
group by argument having count(distinct(user_id)) = 2

+-------------+
| web         |
| nature      |
+-------------+

I thought to do a join with the same table, but i think it's too heavy when the users will be more! don't you? So, something light to use with a lot of "user_id".

Upvotes: 0

Views: 1384

Answers (4)

Vinil Chandran
Vinil Chandran

Reputation: 1561

This sql query will work for you well

select * 
from (
(
    select argument
        ,GROUP_CONCAT(user_id order by user_id) as user_id
    from argument_user
    group by argument
    ) as TEMP
)
group by argument
having user_id = '1,2';

Upvotes: 0

Kickstart
Kickstart

Reputation: 21513

Using a couple of JOINs

SELECT *
FROM argument_user a
INNER JOIN argument_user b
ON a.argument = b.argument
LEFT OUTER JOIN argument_user c
ON a.argument = c.argument
AND c.user_id NOT IN (1,2)
WHERE a.user_id = 1
AND b.user_id = 2
AND c.user_id IS NULL

Upvotes: 1

AnandPhadke
AnandPhadke

Reputation: 13496

try this:

select argument,count(*) from table1
where user_id in ('1','2') 
and argument not in(select argument from table1 group by argument having count(*) <> 2)
group by argument
having count(*)=2

SQL FIDDLE DEMO

Upvotes: 0

Ray
Ray

Reputation: 41428

Assuming user_id is indexed, the IN in your WHERE clause will make this query fast no matter how many users you add to the table.

I would use a join of the table back upon itself vs the having disticnt. Once again the IN clause in your WHERE will make the actual evaluated set handled by the join pretty small (assuming your in statement doesnt have hundreds of users in it!)

Also, if user_id is an integer, don't qoute them in your IN clause--it's a needless type conversion.

Upvotes: 0

Related Questions