CODEWITHSUNDEEP
mysql
DMin
DMin

Reputation: 10353

MySQL query to list common values between 2 users

This table lists events and users that are attending the event :

User         Event
 Joe            Movie
 Joe            Mall
 Jill              Mall
 Joe            Drinking
 Jenn          Drinking
 Jill              Drinking

How can I run a query on this table to list all the events that are common between 2 given users.

My guess is, this will need a self join, but I'm not sure.

Upvotes: 3

Views: 1212

Answers (4)

praveen
praveen

Reputation: 78

select eventname from user_event JOIN user_event as t2 USING (eventname) 
where user_event.username="joe" and t2.username="jill"

Here, 'user_event' is the name of your table and table 't2' is the alias of the 'user_event" table

Upvotes: 0

Joe Phillips
Joe Phillips

Reputation: 51120

SELECT *
FROM myTable joe
JOIN myTable jill ON jill.User='Jill' AND jill.Event=joe.Event
WHERE joe.User='Joe'

This might work also. It can sometimes be more efficient doing it this way (putting all conditions in the JOIN)

SELECT *
FROM myTable joe
JOIN myTable jill ON jill.User='Jill'
    AND jill.Event=joe.Event
    AND joe.User='Joe'

Upvotes: 5

DRapp
DRapp

Reputation: 48139

SELECT * 
   FROM myTable t1
      JOIN myTable t2 
         ON t1.event = t2.event
       and t2.User="Jill"
   where
      t1.user = "Joe"

Upvotes: 1

konsolenfreddy
konsolenfreddy

Reputation: 9671

select event from table group by event having count(user) =2

that won't give you the names of the users though

Upvotes: 0

Related Questions