#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    8
    Rep Power
    0

    My left joins don't work


    I have two tables: menu and perm

    menu:
    menu_id menu_nome
    0 Admin
    1 User
    2 Config

    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:
    menu_nome perm_nome
    Admin read
    User read
    Config

    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?

    thanks
  2. #2
  3. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    It was supposed to return:
    menu_nome perm_nome
    Admin read
    User read
    Config
    Are you sure?
    I recreated your DB from the tables and info you provided and when I run the query you posted I get:

    menu_nome perm_nome
    admin write
    user write
    config write

    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?

    -b
    PostgreSQL, it's what's for dinner...
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    8
    Rep Power
    0

    More Information


    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.
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    8
    Rep Power
    0

    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
    Attached Files
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    8
    Rep Power
    0

    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.

IMN logo majestic logo threadwatch logo seochat tools logo