mysql select a value within a select

Question

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.

I think you have to write a self join query. Select u.uid from tablename u INNERJOIN tablename u1 on u.mid = u1.mid — Waqar Janjua
– Waqar Janjua, Commented Jul 14, 2012 at 10:28

Community · Accepted Answer · 2017-05-23 11:48:13Z

1

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

edited May 23, 2017 at 11:48

CommunityBot

11 silver badge

answered Jul 14, 2012 at 15:13

Jonathan Leffler

759k145 gold badges961 silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sroot Over a year ago

Slight change to who ever will read this answer after the AND replace m1.uid to m2.uid

Waqar Janjua · Accepted Answer · 2012-07-14 15:02:34Z

0

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 ) ) ;

edited Jul 14, 2012 at 15:02

answered Jul 14, 2012 at 10:29

Waqar Janjua

6,1432 gold badges28 silver badges36 bronze badges

7 Comments

sroot Over a year ago

Thanks for the answer , but that would return all messages between users,I would like to receive user_id1 and user_id2 and check whether there is a thread between them

sroot Over a year ago

Well here is what I have done , not sure it the best way but...Select u.mid from tablename u INNER JOIN tablename u1 on u.mid = u1.mid and u1.uid = '1' where u.seq = 1 and u.uid='2' LIMIT 1 , let me know what you think

Waqar Janjua Over a year ago

for that you have to place a where clause at the end of the query lik this.Where ( u.uid In { 1,2 } || u1.uid In { 1.2 } ;see my updated ans.

Waqar Janjua Over a year ago

there is no need to place LIMIT in your query and also no need to include u.seq = 1

ypercubeᵀᴹ Over a year ago

Don't use && and ||. There is an AND and an OR operator in SQL.

|

Collectives™ on Stack Overflow

mysql select a value within a select

2 Answers 2

1 Comment

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related