MySQL SELECT IN with sub-query

Question

Alright, so I am having issue with the following.. I am trying to do a single select statement with two sub queries. What I need it to do is the following pretty much..

SELECT * FROM messages WHERE the following conditions are meet.

condition 1:

 SELECT id 
 FROM messages 
 WHERE `receiver` = 123510 && sender= 123457 && status != 3;

condition 2:

 SELECT id 
 FROM messages 
 WHERE receiver = 123457 && sender = 123510 && status !=4;

I tried doing the following:

SELECT * 
FROM messages 
WHERE id IN (
                 (
                     SELECT id 
                     FROM messages 
                     WHERE `receiver` = 123510 && sender= 123457 && status != 3
                 ),
                 (
                     SELECT id 
                     FROM messages 
                     WHERE receiver = 123457 && sender = 123510 && status !=4)
                  )

but when I do that mysql comes back with Subquery returns more than 1 row...

Any suggestions on how to solve this?

Is there a reason you cannot just or the two clauses together? where (r=1 and s=2 and status != 3) or (r=2 and s=1 and status != 4) — Jim H.
– Jim H., Commented Aug 1, 2012 at 6:06

Omesh · Accepted Answer · 2012-08-01 06:06:49Z

3

I think this is what you are looking for:

SELECT *
FROM messages
WHERE (receiver = 123510 AND sender = 123457 AND status <> 3) OR
      (receiver = 123457 AND sender = 123510 AND status <> 4);

answered Aug 1, 2012 at 6:06

Omesh

29.4k6 gold badges44 silver badges51 bronze badges

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

Comments

pyrospade · Accepted Answer · 2012-08-01 06:12:03Z

0

You will have to separate each query with a union or do it as two separate INs separated with an OR.

SELECT * 
FROM messages 
WHERE id IN (
          SELECT id
          FROM messages
          WHERE `receiver` = 123510 and 
                sender= 123457 and 
                status != 3) or
          UNION
          SELECT id
          FROM messages 
          WHERE receiver = 123457 and 
          sender = 123510 and
          status !=4)

But really, it would be much better to do this.

SELECT * 
FROM messages 
WHERE (`receiver` = 123510 and 
       sender = 123457 and 
       status != 3) or
      (receiver = 123457 and 
       sender = 123510 and
       status !=4)

answered Aug 1, 2012 at 6:12

pyrospade

8,1004 gold badges38 silver badges52 bronze badges

Collectives™ on Stack Overflow

MySQL SELECT IN with sub-query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related