Geek asks and answers

MySQL query to display common values ​​between two users

This table lists the events and users who attend the event:

User Event
  Joe            
Movie Joe Mall
  & Jill NBSP;
  Joe Drinking Shopping Center
  Jenn Drinking
  Jill Drinking

How to run a query in this table to display all events that are common to two users.

I assume that this will require a separate connection, but I'm not sure.

+3
source share
4 answers
SELECT *
FROM myTable joe
JOIN myTable jill ON jill.User='Jill' AND jill.Event=joe.Event
WHERE joe.User='Joe'

. , ( JOIN)

SELECT *
FROM myTable joe
JOIN myTable jill ON jill.User='Jill'
    AND jill.Event=joe.Event
    AND joe.User='Joe'
+5
SELECT * 
   FROM myTable t1
      JOIN myTable t2 
         ON t1.event = t2.event
       and t2.User="Jill"
   where
      t1.user = "Joe"
+1
select event from table group by event having count(user) =2

,

0
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 table "user_event"

0
source

More articles:


All Articles