Distinct recordset query 2

[DouglasF]

I am trying to get a recordset containing a distinct list of all User groups in the userGroups table. If the user_id making the query belongs to any of the groups, flag the 'checked' field as '1', if not flag the 'checked' field as '0'. User_id may belong to none or multiple userGroups.

The query result is to show a list of userGroups with each having a check box showing whether or not the user_id is in the userGroup. This user would be in both the Manager and Admin userGroups.

UserGroup_id | UserGroup | Checked
      1      |  Staff    |      0
      2      |  Manager  |      1
      3      |  Admin    |      1
      4      |  Guest    |      0

ACCESS 2000
Tables:

users
----------
user_id | user_name

userGroups
-------------------------------
userGroup_id | userGroup

user_userGroups
--------------------------
user_id | userGroup_id

#user_id# is the user making the query and passed into the query.

Example 1:

SELECT DISTINCT UserGroups.UserGroup, UserGroups.UserGroup_ID, IIf([users_UserGroups.user_id]=#user_id#,1,0) AS checked
FROM UserGroups LEFT JOIN Users_UserGroups ON UserGroups.UserGroup_ID = Users_UserGroups.UserGroup_ID
ORDER BY UserGroups.UserGroup;

The example above is not correct and returns a non-distinct userGroups list with checked values of 0 and 1.

UserGroup_id | UserGroup | Checked
      1      |  Staff    |      0
      2      |  Manager  |      0
      2      |  Manager  |      1
      3      |  Admin    |      0
      3      |  Admin    |      1
      4      |  Guest    |      0

Example 2:

SELECT DISTINCT UserGroups.UserGroup, UserGroups.UserGroup_ID, IIf(Exists (SELECT  Users_UserGroups.User_ID, Users_UserGroups.UserGroup_ID FROM Users_UserGroups
WHERE Users_UserGroups.User_ID=#user_id# AND Users_UserGroups.UserGroup_ID= UserGroups.User_ID),"1","0") AS checked
FROM UserGroups LEFT JOIN Users_UserGroups ON UserGroups.UserGroup_ID = Users_UserGroups.UserGroup_ID
ORDER BY UserGroups.UserGroup;

This attempt results in a distinct list of the four userGroups, but with all checked = 0.

UserGroup_id | UserGroup | Checked
      1      |  Staff    |      0
      2      |  Manager  |      0
      3      |  Admin    |      0
      4      |  Guest    |      0

Any suggestions on where I am going wrong?

Thanks!

 

[lupins46]

I have only looked at the first query.
The result will contain one record for every existing user/usergroup combination plus one record every usergroup for which there there is no user.
You are not limiting the records to those which match the entered user number, you are only limiting the display of the checked value to those which match the entered user number.

If you want to show all the groups but limit the records to those which match the entered userid then you need to use a subquery to select the records which match the entered userid and then use a left join from the usergroups table to this subquery.

 

[lupins46]

Incidentally this is incorrect although might just be a typo in your Q:
IIf([users_UserGroups.user_id]
should be:
IIf([users_UserGroups].[user_id]

and although I suspect your use od #user-id# might work here you should be aware that the #..# symbol in JetSQL is use to delimit dates and you could be asking for trouble if you use it elsewhere.

 

[PHV]

Something like this ?
SELECT G.UserGroup, G.UserGroup_ID, IsNull(I.user_id) AS checked
FROM UserGroups G LEFT JOIN (
SELECT * FROM Users_UserGroups WHERE User_ID=#user_id#
) I ON G.UserGroup_ID = I.UserGroup_ID
ORDER BY G.UserGroup;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DouglasF]

Thanks Lupins46

Your correct in that I want to show ALL the userGroups but I do NOT want to limit the records to those which just match the entered user_id. The record set needs to include ALL userGroups and also create and set the 'checked' field for each userGroup to '1' for userGroups that match the user_id OR set the 'as checked' field to '0' for userGroups that do not match the user_id.

Yes the [] is a typo and the #user_id# is on a coldfusion template so is converted to the actual value before ACCESS sees it.

Thanks, DF.

 

[lupins46]

Then I can't see anything wrong about the result of query1.
What do you think is wrong with it?

 

[DouglasF]

Thanks PHV,

Your suggestion works perfectly! A little sleep has helped me see a little clearer...

I needed the subquery to set the 'checked' field for each userGroup. The LEFT JOIN allows all userGroups to be returned with each having a 'checked' field set to true or false depending on the #user_id# var passed to the query. I added the Iif() to reverse the case of the 'checked' value to help clarity in the template that uses the record set.

Thanks again!
Douglas

Final correct code:
<code>
SELECT G.UserGroup, G.UserGroup_ID, IIf(IsNull(I.user_id),"0","1") AS checked
FROM UserGroups AS G LEFT JOIN (SELECT * FROM Users_UserGroups WHERE User_ID=#user_id#) AS I ON G.UserGroup_ID = I.UserGroup_ID
ORDER BY G.UserGroup;
</code>

 

[DouglasF]

lupins46 (MIS) 4 Apr 05 11:00
Then I can't see anything wrong about the result of query1.
What do you think is wrong with it?

Hi lupines46,
My example query1 returns a non-distinct list of user groups instead of four different user groups with the 'checked' field set to true or false such as:

UserGroup_id | UserGroup | Checked
      1      |  Staff    |    0   
[COLOR=red yellow]      2      |  Manager  |    0   
      2      |  Manager  |    1   
      3      |  Admin    |    0   
      3      |  Admin    |    1   [/color]
      4      |  Guest    |    0

instead of only the four different user groups with the 'checked' field set to true or false such as:

UserGroup_id | UserGroup | Checked
      1      |  Staff    |    0
[COLOR=red yellow]      2      |  Manager  |    1   
      3      |  Admin    |    1   [/color]
      4      |  Guest    |    0

I needed to do a subquery on the user_userGroups table to find the users assigned to a user group and the do a left join to the userGroup table.

Thanks,
DF