query help to display users

[andydeans]

hi,

i have been trying to work out how to list all the users on the database for my website with a particular access level and also only the user id that the client is associated to however i end up with duplicated users.

here is my query so far

SELECT clients.ClientID, clients.ClientUserID, clients.AddedByUserID, users.UserID, users.FullName, users.AccessLevel
FROM users LEFT JOIN clients on users.UserID = clients.ClientUserID
WHERE users.AccessLevel = 1  or (clients.ClientUserID = users.UserID)and clients.ClientID = ParamClientID

clientUserID is the person who the client is assigned to.

access level 1 is admins where as the user whose client only has access level 2 so i only want the user whose client is to show in the drop down list.

can anyone shed any light on where i amgoing wrong?

thanks

[d5e5]

I haven't tested it but I think that either using DISTINCT or GROUP BY should eliminate the duplicate rows from your results. Try

SELECT DISTINCT clients.ClientID, clients.ClientUserID, clients.AddedByUserID, users.UserID, users.FullName, users.AccessLevel
FROM users LEFT JOIN clients on users.UserID = clients.ClientUserID
WHERE users.AccessLevel = 1  or (clients.ClientUserID = users.UserID)and clients.ClientID = ParamClientID

or else maybe

SELECT clients.ClientID, clients.ClientUserID, clients.AddedByUserID, users.UserID, users.FullName, users.AccessLevel
FROM users LEFT JOIN clients on users.UserID = clients.ClientUserID
WHERE users.AccessLevel = 1  or (clients.ClientUserID = users.UserID)and clients.ClientID = ParamClientID
GROUP BY clients.ClientID, clients.ClientUserID, clients.AddedByUserID, users.UserID, users.FullName, users.AccessLevel

[andydeans]

thanks i tried both bbut no joy, however when i used the

GROUP BY users.UserID, users.FullName

that seems to work.

is there anything wrong with using this group by as opposed to your example?

just want to make sure it is ok.

many thanks again for your help

[d5e5]

As long as the duplicate rows you were getting had the same values for the columns NOT in your GROUP BY it should not cause you a problem. MySQL allows you to have columns in your SELECT that are not in your GROUP BY, but their values are arbitrarily chosen from the rows that are grouped. If the values of these non-GROUP BY columns are the same within groups, then no problem.

Other databases are not so permissive and some writers recommend that you avoid this in MySQL as well. For example, see http://www.mysqlperformanceblog.com/2006/09/06/wrong-group-by-makes-your-queries-fragile/