Select in table records with Mxn relation

Question

Hello, I have in my bank a table tb_usuario (id INT, name VARCHAR, flag_active INT) and a table tb_regional(id INT, name VARCHAR,login VARCHAR, password VARCHAR). From the relationship between the two arises the table tb_regional_usuario(tb_regional_id INT, tb_usuario_id INT). Well, I would like to know how to mount a query that where I enter the user id and the bank returns me the regional ones for which it has access permission. Thank you in advance.

Answer 1

Just make a INNER JOIN between the three tables:

SELECT r.id, r.nome
FROM tb_usuario u
INNER JOIN tb_regional_usuario ru ON ru.tb_usuario_id = u.id
INNER JOIN rb_regional r ON r.id = ru.tb_regional_id
WHERE u.id = :tb_usuario_id

Answer 2

In this case your table is structured like this:

|-----tb_usuario-------|
|id - nome - flag_ativo|
|----------------------|

|--------tb_regional------|
|id - nome - login - senha|
|-------------------------|

|------tb_regional_usuario-----|
|tb_regional_id - tb_usuario_id|
|------------------------------|

To bring the desired data you can use inner join.
Would look like this:

select *
from tb_usuario
inner join tb_regional_usuario on (tb_usuario.id = tb_regional_usuario.tb_usuario_id)
inner join tb_regional on (tb_regional_usuario.tb_regional_id = tb_regional.id)
where tb_usuario.id = 5; //TROQUE O ID PELO DESEJADO

I hope I’ve helped.