July 17th, 2003, 09:43 AM
My left joins don't work
I have two tables: menu and perm
perm_id perm_nome menu_id user_id
0 write 0 0
1 write 1 0
2 write 2 0
3 read 0 1
4 read 1 1
As i run the query:
select menu.menu_nome, perm.perm_nome
from menu left join perm using(menu_id) where user_id = 0;
It was supposed to return:
But it doesn't. It returns only the first two lines, whihc have data on the perm_nome column. the third column, which has null on the perm_nome column, is not returned.
I don't know what's wrong. As far as i know, postgresql is able to perform this type of query. But i can't seem to find a way for that to work.
Can anybody help me please?
July 17th, 2003, 11:51 AM
Are you sure?
I recreated your DB from the tables and info you provided and when I run the query you posted I get:
which is what I'd expect from that query since there are no perms with read where user_id = 0 in your data Can you provide more info on what you're trying to do with your query?
PostgreSQL, it's what's for dinner...
July 17th, 2003, 01:26 PM
OK, this is what i'm trying to do:
I have to build an access control to an it system.
I have one table ('menudecontrole') inside which each line is the representation of a specific area within the system.
There's another table with the information of the users ('usuarios'). Another table is 'niveis', which has the levels of access (none, read and write).
The final table of this structure is 'permissoes'. 'permissoes' has only foreign keys of the first three tables. This means that 'permissoes' tells me which user has what permission or level of access to which area.
That being said, i must add that 'permissoes' may have holes. That means that one user may has its permissions, but a new area can be created after that. Therefore, the user has no permission defined to that new area until someone inserts a register inside 'permissoes'.
So, when i query 'permissoes', i want to know what are allt the permissions to an specific user. If the permission to an specific area is not set yet, i want to have a null field returned.
is that clear? If not, let me know so i can help you help me.
thanks a lot
* table 'menudecontrole' has the same structure as Control Menu os MS Access.
July 17th, 2003, 01:34 PM
DB structure and relationships
The structure of the tables and its relationships are in the attached file.
I'm trying to run this query:
select menudecontrole.textoitem, permissoes.usuario_id, permissoes.nivel_id
from menudecontrole left join permissoes using(menu_id)
where usuario_id = 1
order by painel_id, numeroitem;
Thanks a lot
July 17th, 2003, 02:09 PM
It's not gonna work
That thing that i'm trying to do will never work. At least not the way that i thought i could.
Your query worked the way i want because there were no lines in the right table with a correspondind register in the left. Thats what the left join does.
If my table 'permissoes' would have data regarding only one user, that would do, but there wil be data regarding all the users.
It's hard for me to explain that without showing examples, butnow i know i have to figure out another way to do that.
thanks a lot.