0

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
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user3622259
  • 1
  • 1

2 Answers2

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:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

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

user3622259
  • 1
  • 1