SQL Question

Thanks Chuck!

Read the page, was good, but didn’t make me come any closer to the solution.

Maybe I have to redesign the query completely, but I hope not. If so I’ll have to rewrite my largest function ever, 150 lines.

Hopefully someone finds out why it’s not working, I’ll keep trying too.

I may be silly, but would a LEFT JOIN work?

what about this:

this might work too:

@ Chuck: Which table/tables should I use left join on? I can’t easily see how that would help as there always will be data in all the tables as soon as a user is registered.

@ kylej, thanks for your suggestions. Haven’t tested them yet, been sitting in front of computer to long time, everything is spinning

The second suggestion seems better for me as I would prefer not to have to much code in the "where statement". The query I’m showing here is just a part of the whole query (which have two more tables and more where-statements). Also, the query is built by pretty complex php logic depending on users choices, to keep that code not becoming to complex it would be great if the HAVING statement you suggesting worked.

But as said, my head is spinning, need one nights sleep before I contine testing

Thanks all for your help!

(Btw, more suggestions are welcome Maybe a subquery in the SELECT could be used for connecting user/group/groupname? Bad idea. )

EDIT: Also tried a subquery as this:
WHERE n.name = ALL (SELECT name FROM `db1`.modx_webgroup_names WHERE name=(’Registered Users’ OR ’OtherRegUsers’))
No error, no result. I’m beginning to believe the problem is... hmmm... some stupid mistake

Quote from: Uncle68 at Nov 22, 2006, 09:20 AM

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?

[Warning: I know SQL but not the MODx DB structure (yet) so my comments are general]

The problem here is that you are forgetting that SQL always processes everything ROW-BY-ROW ... the joins ensure that each single row is referring to a particular (user, attributes,name,group) combination. In particular a user belonging to two groups will appear as TWO rows, one for group 1 and one for group 2. These will be processed separately so the purple line will always ensure that NOTHING matches because it is asking that a single field (n.name) has two different values at the same time.

If you just want distinct usernames then you could just do

SELECT DISTINCT u.username FROM ...

The reason it doesn’t work if you include the other stuff

SELECT DISTINCT u.username, a.*, g.* FROM ...

is that the "DISTINCT" means that if there is a difference in ANY ONE of the fields, then it is considered to be a distinct row... so again, the fact that the single user appears with multiple groups will automatically make the two rows different.

HTH

Gordon

Thank you Gordon!

Of cource, I should have figured that out, stupid me!

I’ll try come up with a solution that works together with my code that creates the query this weekend.

Again, thanks all!