left join question

[Stooney]

Before anything, here's my table layouts.

 

permissions
    perm_id
    perm_desc
    perm_name
    perm_key
    perm_min_level
menu_items
    item_id
    perm_id
    item_key
    item_dname
user_permissions
    uperm_id
    user_id
    perm_id
    perm_level

 

Now, 'permissions' is a table that stores permission settings for various parts of the site.  'menu_items' is where menu links are retrieved from.  Users will see menu items depending on their level.  'user_permissions' is a table with settings which overwrite their user_level.  So if a user has a user_level of 2, but I want to grant them access to something requiring 3, but only to that particular thing, it would go here. 

 

So I need the query to pull their menu items from the database, accounting for the possible overwrite values from user_perms.  I have the following query which is almost what I need, but lacks the overwriting aspect, which I can't figure out

 

SELECT m.item_dname as name, m.item_key as key
FROM menu_items as m
    LEFT JOIN permissions as p USING (perm_id)
    LEFT JOIN user_permissions as up USING (perm_id)
WHERE '$user_level'>=p.perm_min_level

 

Do I need two separate queries for this or is there a way to squeeze it all in one?

 

p.s. Thank You Daniel for your join tutorial. 

[Ken2k7]

First of all, key is a MySQL reserved word.

 

You'll need the user_id for this, but you don't need the $user_level variable assuming that your user's table has a user_level column.

 

This isn't tested.

SELECT mi.item_key as `key`, mi.item_dname as name
FROM menu_items mi
JOIN users u ON u.user_id = 4
JOIN permissions p ON mi.perm_id = p.perm_id
JOIN user_permissions up ON p.perm_id = up.perm_id
WHERE u.user_level >= p.perm_min_level