Query multiple tables in PostgreSQL (LEFT or RIGHT and other)

Question

I have 3 table in my PostgreSQL database:

menu(id_menu,parent_id,name_uri,uri)
type_role(id_role,name_role)
menutyperole(id_menu,id_role)

In my table type_role I have the following data (administrator and technician), in the table menu exist has many link multi-level to access a other sides, and the table menutyperole is a connection with the other tables. I need a query to extract the data of menu where no technician is assigned (type_role table). I don’t know if I need any condition or anything else?

SELECT * 
FROM menutyperole 
     INNER JOIN type_role ON menutyperole.id_role = type_role.id_role 
     RIGHT JOIN menu ON  menu.id_menu = menutyperole.id_menu 
WHERE menu.id_parent != 0

I request you to provide sample data for each table and the expected output in the question — Vivek S., Apr 04 '15 at 04:49

score 0 · Answer 1 · edited May 23 '17 at 11:57

0

One way (of many) to get rows from menu that have no type_role assigned:

SELECT m.* 
FROM   menu m
LEFT   JOIN menutyperole mr USING (id_menu)
WHERE  m.id_parent <> 0
AND    mr.id_menu IS NULL;

More:

Select rows which are not present in other table

edited May 23 '17 at 11:57

Community

1
1

answered Apr 04 '15 at 04:53

Erwin Brandstetter

605,456
145
1,078
1,228

score 0 · Answer 2 · answered Apr 04 '15 at 17:28

i solved the problem... was this

SELECT * FROM menu INNER JOIN type_role ON type_role.id_role NOT IN(SELECT menutyperole.id_menu FROM menutyperole) LEFT JOIN menutyperole ON menu.id_menu = menutyperole.id_menu WHERE menu.id_parent != 0 AND menutyperole.id_menu IS NULL OR menutyperole.id_role != 1

Query multiple tables in PostgreSQL (LEFT or RIGHT and other)

2 Answers2