CODEWITHSUNDEEP
mysqlsqlselect
sroot
sroot

Reputation: 53

mysql select a value within a select

I have an implementation messages system. My problem is, I would like to know whether a user already has a thread with another user and if so what is the mid

I have a messages_recips table which look like this

---------------------------     
| mid | seq | uid | status|
|--------------------------
| 4   | 1   | 1   | A     |
| 4   | 1   | 2   | A     |
---------------------------

if user id 1 having a thread with user id 2 I hold 2 rows with same mid. I know I can create 2 sqls to achieve what I'm asking for, but I'm trying to do it in 1 sql.

Upvotes: 1

Views: 124

Answers (2)

Jonathan Leffler
Jonathan Leffler

Reputation: 755064

As noted by Waqar Janjua, the key to this is a self-join query:

SELECT m1.mid
  FROM messages_recips AS m1
  JOIN messages_recips AS m2 ON m1.mid = m2.mid
 WHERE m1.uid = 1
   AND m2.uid = 2

Upvotes: 1

Waqar Janjua
Waqar Janjua

Reputation: 6123

I think you have to write a self-join query:

Select u.uid, u1.uid from tablename u
 INNER JOIN tablename u1 on u.mid = u1.mid

You will get all the users who have the same mid.

In order to get only user1 and user2 records you have to place a where clause at the end of the query lik this.

 Select u.uid, u1.uid from tablename u
 INNER JOIN tablename u1 on u.mid = u1.mid
 Where ( u.uid In ( 1,2 ) OR u1.uid In ( 1,2 ) ) ;

Upvotes: 0

Related Questions