CODEWITHSUNDEEP
mysql
Ran Loock
Ran Loock

Reputation: 3

Query between multiple rows mysql

I'm totally new to mysql.

I'm trying to determine a query that would check my database for matches, when myid = hisid, and hisid=myid between different rows.

I understand why this code:

SELECT * FROM table 
WHERE myid = hisid

Didn't work, because what I'm looking for is in different rows.

An example table:

id| myid| hisid|
__|_____|______|
1 |  1  |  2   |
2 |  1  |  3   |
3 |  2  |  1   |

The output should be id 1 and id 2.

Thanks everybody in advance!

Upvotes: 0

Views: 41

Answers (3)

Kamal Aujla
Kamal Aujla

Reputation: 327

For such cases, use Join operation. Query will be: 

    SELECT * FROM table1 INNER JOIN table2 ON  table1.myid=table2.hisid

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37357

Try this:

select * from `table` t
where exists(select 1 from `table`
             where hisid = t.myid)

Upvotes: 0

JNevill
JNevill

Reputation: 50034

You'll join the table to itself:

SELECT * 
FROM table t1
   INNER JOIN table t2
      ON t1.myid = t2.hisid

Upvotes: 1

Related Questions