[SOLVED] Query - Thought this would be easy... but MY GOD!!! Grrrr

[jetsettt]

MySQL Version 5.0.67

 

Thought this would be an easy query... but I just can't crack it.

 

What I am trying to achieve

I have a site that organises members into seperate groups. As the site has developed, some members now need access to other groups. To achieve this I add permission for a member to access a group when they need it by simply storing the members ID and the required Group ID in a permissions table, easy. The big problem I am having is retrieving the permissions correctly for the member to administer themselves.

 

I need to retrieve a list of ALL the groups and the matching permission entry (if any) for the logged in member (ID from session) alongside the group on the same row. I have tried LEFT JOIN which works fine but that brings back other member IDS which have permissions for the group which I don't need. I just need a corresponding table entry (or null) for the logged in member. I thought of using GROUP_CONCAT and filtering out the members ID on the page using PHP but that is constricted by its maximum length and I don't want to mess around extending the value.

 

I want the output to look like this to the member:

 

Name              Accessible            Manage Access?

Group 1                Yes                      Click Here >

Group 2                No                        Request >

Group 3                Yes                      Click Here >

 

Query I have

SELECT
group.group_id,
group.group_name,
permissions.permissions_user,
permissions.permissions_group,
permissions.permissions_id,
permissions.permissions_live
FROM
schemes
Left Join permissions ON group.group_id = permissions.permissions_group
WHERE
permissions.permissions_user ='2' OR permissions.permissions_user IS NULL 

 

Query results

group_id  group_name      permissions_user permissions_group permissions_id permissions_live

1       Group 1           (null)         (null)                      (null)              (null)

3       Group 3              2                   2                         5                 1

 

Problem for me...

Its doesn't return the FULL list of groups!. Group 2 is missing. I guess it is because there is no entry in the permissions table for 'permissions_user 2' to access 'Group 2' (shown below). I can't do it though...

 

 

This is what the 'permissions' table holds

group_id  group_name      permissions_user permissions_group permissions_id permissions_live

2         Group 2                    1                     1                            4                 1

3         Group 3                    1                     1                            3                 1

3         Group 3                    2                     2                           5                 1

 

 

Table structures

Permissions

permissions_id

permissions_user

permissions_group

permissions_live

 

group

group_id

group_name

 

 

 

I really can't find a way of retrieving the full list of groups and then ONLY the matching (or show null) members ID on the same row from the permissions table. Can anyone help as I have been on this for days! 

 

Thanks in advance

 

I hope this all makes sense (it is very late!)

 

 

 

[abazoskib]

i dont think you need to use a join to do that. im thinking you should get all the groups, then get the users applicable permissions. since you want to show all the groups,

[kickstart]

Hi

 

Move the clause that excludes the users from the permissions table into the ON clause:-

 

SELECT

group.group_id,

group.group_name,

permissions.permissions_user,

permissions.permissions_group,

permissions.permissions_id,

permissions.permissions_live

FROM

group

Left Join permissions ON group.group_id = permissions.permissions_group AND permissions.permissions_user ='2'

 

All the best

 

Keith

 

PS I assume that the tables schemes is meant to be the table groups in your sql.

[jetsettt]

Thanks for the reply abazoskib but I have just sorted it with Keiths advice!

 

Keith, Thankyou very,very much, it worked!!!!

 

I didn't even think of putting the members ID into the ON clause. I'm not sure I've ever done that! and I can see I will be using that alot from now one.

 

Thankyou. 

 

Ps: the 'schemes' is the groups table.

 

 

[kickstart]

I didn't even think of putting the members ID into the ON clause. I'm not sure I've ever done that!

 

Checking against constants in the ON clause is something that isn't supported in all flavours of SQL (M$ Access doesn't support it).

 

Occasionally useful in outer joins where you want null records when the join would otherwise bring back a matching record which you would ecclude in the WHERE clause.

 

All the best

 

Keith

[jetsettt]

Very useful to me Keith. It has always been a problem for me when NULL Records don't return when I needed them to keep a full list returned. It was very frustrating when only the matching records came back and the NULL results didn't.

 

Now that I know this is possible, I am off to swot up on this.

 

Thanks again.