SQL Question

I’m trying to select users for listing, and I want to be able to select users based on groups. I have the following db query (don’t laugh, got to start somewhere ).

SELECT u.username, a.*, g.*, n.*
FROM `db1`.modx_web_users u
INNER JOIN `db1`.modx_web_user_attributes a ON u.id = a.id
INNER JOIN `db1`.modx_web_groups g ON u.id = g.webuser
INNER JOIN `db1`.modx_webgroup_names n ON g.webgroup = n.id
ORDER BY username LIMIT 5

It works, but if a user is in more than one group he/she gets listed more than once.

If I specify WHERE = ’GroupName’ the user only get listed once, but I don’t always want to specify a group. I only want the user to be listed one time.

If someone could suggest how to do I’d be a happy puppy!

Also, if someone want to suggest me how to write this sql in a better way, please go ahead!

SELECT DISTINCT username?

Thanks for the reply. This was the first I tried, unfortynatly without success.

I’ll keep trying, maybe one night good sleep helps me solve it

SELECT u.id, u.username
FROM `db1`.modx_web_users u
INNER JOIN `db1`.modx_web_user_attributes a ON u.id = a.id
INNER JOIN `db1`.modx_web_groups g ON u.id = g.webuser
INNER JOIN `db1`.modx_webgroup_names n ON g.webgroup = n.id
WHERE n.name IN (’Webgroup1’,’Webgroup2’,’Webgroup3’)
WHERE n.name = ’Webgroup1’ and n.name = ’Webgroup2’ and n.name = ’Webgroup3’
GROUP BY u.id, u.username
ORDER BY username LIMIT 5

if you need more fields from the user attributes table you should include them in the group by clause each by name
the green addition will get you all users that are in of of these groups.
the purple addition will get you the users that are in all groups.

there is a better way of doing this btw but i just took your example to make a fast post.

Thank you very much Armand!

The code I posted wasn’t the whole query, so I need to adjust your sample a bit, but now I know how I could solve it!

For now I just want it to work, later I’ll rewrite it to be "good code", but at this moment I just want to finishing up so I can post next version of PPP.

Again, thanks for taking the time, you’re my coding idol

Armand (or anyone else),

I followed your suggestion, but there is one problem:

SELECT u.username, a.*, g.*, n.* FROM `db1`.modx_web_users u
INNER JOIN `db1`.modx_web_user_attributes a ON u.id = a.id
INNER JOIN `db1`.modx_web_groups g ON u.id = g.webuser
INNER JOIN `db1`.modx_webgroup_names n ON g.webgroup = n.id
WHERE n.name IN (’Registered Users’, ’OtherRegUsers’)
WHERE n.name = ’Registered Users’ AND n.name = ’OtherRegUsers’
GROUP BY u.id, u.username ORDER BY username

If I use the line in green everything works, but if I use the purple line nothing is returned at all (and no errors).

Any suggestions?

Maybe the purple AND should be an OR?

How about:
WHERE n.name IN(’Registered Users’) AND n.name IN(’OtherRegUsers’)

Susan, I need this to work so that the user must be in both groups to be listed, so I can’t use "OR".

TobyL, tried it, got the same result as in the purple line in my code above, no result, no error.

These are the variants I’ve tested:

WHERE n.name IN (’Registered Users’ AND ’OtherRegUsers’) = Gives all users, even if not member in OtherRegUsers

WHERE n.name IN (’Registered Users’) AND n.name IN (’OtherRegUsers’) = No result, no error (just as in the purple line in my first example)

WHERE n.name = ’Registered Users’ AND n.name = ’OtherRegUsers’ = (As suggested by Armand) No result, no error

I’ll keep on testing, maybe it can’t be done this way at all

Thanks for trying!

You might have covered this, but here is a great page I have used when looking for advanced SQL samples:

http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php (it’s down near the middle/bottom of the page).

Chuck