SQL query help

[esport]

Hey, need some help...
I have 3 tables:
1. users (userID, user_level)
2. categories (catID)
3. permissions (catID, userID)

I want to be able to view all values in the catergories table for a particular userID where it is set in the permissions table. However if the user_level is set to 1 in the users table, they may not exist in the permissions table, but i still want to view all categories. How do i go about this query.

Thanks

Daniel

[shoz]

Post an example of the data in the tables and an example of the output you want.

Also post the version of MYSQL you're using. If you don't know use the following query
[code]
SELECT VERSION()
[/code]

[esport]

Thanks for the reply, i think its an easy one...

Example table.

users (userID, name, user_level)
(user1, Daniel, 1)
(user2, Simon,0)

categories(catID,name)
(1, Office)
(2, Rules)
(3, Procedures)
(4, Gallery)

permissions(username,catID,permission)
(user2,2,1)
(user2,4,1)

The idea is when the user logs in, the categories are displayed but are determined weather it has been set in the permissons table and the if ther user_level is equal to 1. If the user level is set to 0, the only categories displayed are the ones set in the permissions table.

Im trying to write a query that will incorporate all that into it. I have done a basic join but because a user has its user_level to 1 and is not in the permissions table, the query doesnt produce a result.

Im using version 4.0.27

Thanks heaps

Daniel

[shoz]

I've come up with this query as an exercise only. I wouldn't recommend using it. It would most likely be slower than performing 2 queries where you handled the logic in PHP.
[code]
SELECT
c.*
FROM
users AS u
LEFT JOIN
permissions AS p
ON
IF(u.user_level=1,0,p.userID=u.userID)
LEFT JOIN
categories AS c
ON
IF(u.user_level=1,1,c.catID=p.catID)
WHERE u.userID=$userID;
[/code]

I'd recommend you do the following in PHP
[code]
SELECT user_level FROM users WHERE userID = $userID
if (user_level == 1)
{
    SELECT c.* FROM categories;
}
else
{
    SELECT
    c.*
    FROM
    permissions AS p
    INNER JOIN
    categories AS c
    ON c.catID=p.catID
    WHERE p.userID=$userID;
 
}
[/code]

[esport]

Hey thanks fpr the reply.
I tried it but i keep getting a mysql error when using the if statement. ??

Check the manual that corresponds to your MySQL server version for the right syntax to use near 'if (user_level == 1) { SELECT c.* FROM c...

Thanks

Daniel

[shoz]

[quote author=esport link=topic=101752.msg403059#msg403059 date=1153815333]
Hey thanks fpr the reply.
I tried it but i keep getting a mysql error when using the if statement. ??

Check the manual that corresponds to your MySQL server version for the right syntax to use near 'if (user_level == 1) { SELECT c.* FROM c...

Thanks

Daniel
[/quote]

The second snippet I posted was a guide on how you could do this programmatically, not a query.

[code]
Retrieve the user_level for the user based on their userID

if their user_level is the same as 1
{
    Retrieve all the categories from the categories table
}
else
{
    retrieve the categories that have an entry in the permissions table for the user
}
[/code]

[esport]

Thanks heaps,
yeh i thought the second snippet was for PHP use, however i have managed to successfully use your first snippet and it works great.

Thanks for your help, much appreciated.

Daniel