al_kasih_empatix al_kasih_empatix - 5 months ago 7
SQL Question

Viceversa in mysql query clause

I want to select record based on two conditional

SELECT *
FROM record
WHERE FromPersonID in (1,2) AND
ToPersonID in (1,2)


However this query can give me a record like this:

+--------+----------------+----------------
| id | FromPersonID | ToPersonID |
+--------+----------------+----------------
1 1 2
2 1 1
3 2 2
4 2 1
5 1 2


In the id of 2 and 3, the record has the same value in FromPersonID and ToPersonID

What I want is that it will only check the viceversa between the two field.

Can any body help to acchieve this?

Thank you.

Answer

If you want to get

     2             1              1
     3             2              2


SELECT *
  FROM record
  WHERE FromPersonID in (1,2) AND
  ToPersonID in (1,2) and FromPersonID=ToPersonID;

else if you want to get RECORDS WHICH HAVE DIFFERENT VALUE FOR THOSE TWO COLUMNS

SELECT *
      FROM record
      WHERE FromPersonID in (1,2) AND
      ToPersonID in (1,2) and FromPersonID!=ToPersonID;



SELECT * FROM TT;
+------+--------------+------------+
| ID   | FromPersonID | TOPERSONID |
+------+--------------+------------+
|    1 |            1 |          2 |
|    2 |            1 |          1 |
|    3 |            2 |          2 |
|    4 |            2 |          1 |
|    5 |            1 |          2 |
+------+--------------+------------+
5 rows in set (0.00 sec)

 SELECT *       FROM TT       WHERE FromPersonID in (1,2) AND  TOPERSONID in (1,2) and FromPersonID!=TOPERSONID;
+------+--------------+------------+
| ID   | FromPersonID | TOPERSONID |
+------+--------------+------------+
|    1 |            1 |          2 |
|    4 |            2 |          1 |
|    5 |            1 |          2 |
+------+--------------+------------+
3 rows in set (0.00 sec)
Comments