Search Interlaced ID (mutual), sql mysql

Asked

Viewed 34 times

1

I have a table with this format

RELATIONS
-----------------
ID | ID_1 | ID_2

all in INT and the ID is self incrementable.

She’s filled this way:

ID | ID_1 | ID_2
----------------- 
1  |  1   |  5
-----------------
2  |  5   |  1
-----------------
3  |  1   |  3
-----------------
4  |  3   |  6
-----------------
5  |  6   |  3
-----------------

Have you noticed that there are intertwined IDS? The 1 and the 5 are connected in both the first and the second ways, the 3 and the 6 also but the 1 and the 3 no, why are not mutual connections..

How to take the interlaces (which are mutual in the first and second way) of the id number 6? or of 5, or 1?

For example I want to get the ID Loop ID 6

ID_PROCURADO | ENTRELAÇADO
---------------------------
6            |  3
mysql sql

1 answer

2


I believe that’s it:

select t1.id_1 as procurado, t1.id_2 as entrelacado
from teste t1
inner join teste t2 on t1.id_1 = t2.id_2 
                   and t1.id_2 = t2.Id_1
where t1.id_1 = 6

Because of INNER JOIN will only return the entwined.

Sqlfiddle Example

  • Great! haha, how can I get the id of the entrelacado to play in another table? I tried but did not succeed, see: select U.*, t1.id_1 as procurado, t1.id_2 as entrelacado
from teste t1, users U
inner join teste t2 on t1.id_1 = t2.id_2 
 and t1.id_2 = t2.Id_1
where t1.id_1 = 6 AND U.id = entrelacado

  • When I start to work on something first I work with prototypes until I move to the final version, in this prototype the table is called Yes test, and it worked perfectly, I just wish it was the table USERS and related the ID with the ENTRELACADO, or rather say USERS.ID = ENTRELACADO

  • you want to make an Insert in another table with the interlaced ID?

  • A select in the case..

  • in my example is the column t1.id_2, it will always return the ID of the interlaced

  • Yes, but in the query I mounted and showed you above, even replacing me returns a mysql error

  • pq you have to do u.id = t1.id_2 and not interlocked

  • It keeps on giving error.. See even Voce..

  • what error is giving?

  • ago select * from users u where u.id in ( select t1.id_2 as entrelacado from teste t1 inner join teste t2 on t1.id_1 = t2.id_2 and t1.id_2 = t2.Id_1 where t1.id_1 = 6)

Show 5 more comments

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.