Select in table records with relation MxN
0
Hello, I have in my database a table tb_usuario (INT id, VARCHAR name, INT flag_active) and a table tb_regional (INT id, VARCHAR name, VARCHAR login, VARCHAR password) . From the relationship between the two the table tb_regional_user (tb_regional_id INT, tb_usuario_id INT) appears. Well, I'd like to know how to set up a query that where I enter the user id and the bank returns me the regional ones to which it has access permission. Thank you in advance.
asked by anonymous 12.07.2016 / 14:53
2 answers
0
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 get the desired data you can use inner join .
It 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 have helped.
12.07.2016 / 15:11
0
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
12.07.2016 / 15:00