>Hi there
>
>the case:
>- one table with users (tblUser)
>- one table with groups (tblgroups)
>- one table with user-group links (tbluserlink) - describes which groups one
>user belongs to, in a many-to-many relationship
>
>
>the question:
>I want to make a select where I get all the users, that are in the same
>groups as myself. How do I do this with multiple joins, when I can't do a
>select in(select)?
>
>
>Thanks in advance!
>
>Regards,
>/Henrik Mohr
Sir, I still haven't gotten MySQL set up again after my last Windows
crash, so I can't test this. But I believe it will work.
SELECT DISTINCT user2.*
FROM ((tbluser AS user1 INNER JOIN tbluserlink AS link1
ON user1.id = link1.user_id
) /* This join gives you all the IDs for groups that you belong to. */
INNER JOIN tbluserlink AS link2
ON link1.group_id = link2.group_id AND link1.user_id <> link2.user_id
) /* This join gives you the link records for all people in the
same groups
with you. The 2nd condition should remove you from the list. */
INNER JOIN tbluser AS user2
ON link2.user_id = user2.id /* And there's the tbluser records for
people in your groups.*/
WHERE user1.name = 'Henrik Mohr';
I believe the condition that removes you from the list will work in
the join condition. If not, you will have to move it to the WHERE
clause. Or maybe you don't want to be removed from the list.
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
