ONE SQL-query?
>>
>> What would the query look like?
>>
>> The following query would only get me all USERs having ROLE 1:
>>
>> SELECT * FROM USER WHERE id IN (SELECT DISTINCT USER_ROLE.user_id FROM
>> USER_ROLE WHERE USER_ROLE.role_id = 1);
>>
>>
Rapthor,
Try ...
SELECT u.name
FROM user u
JOIN user_role ur ON u.id=ur.user_id AND (u.id=1 OR u.id=2)
JOIN user_group ug ON u.id=ug.user_id AND (u.id=1 OR u.id=2)
HAVING COUNT( DISTINCT ur.role_id ) = 2
AND COUNT( DISTINCT ug.group_id ) = 2;
BTW you can't name a table 'group'; it's a reserve
query would only get me all USERs having ROLE 1:
SELECT * FROM USER WHERE id IN (SELECT DISTINCT USER_ROLE.user_id FROM
USER_ROLE WHERE USER_ROLE.role_id = 1);
Thanks in advance!
I really need help with this! Thanks again.
--
View this message in context:
http://www.nabble.com/Many-to-many
- Original Message -
From: "Emily Lena Jones" <[EMAIL PROTECTED]>
To: "Rhino" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Thursday, October 28, 2004 7:46 PM
Subject: Re: many-to-many query
>
> Quoting Rhino <[EMAIL PROTECTED]>:
>
Quoting Rhino <[EMAIL PROTECTED]>:
> I wasn't sure if you understood the concept of association tables so forgive
> me if I told you things you already knew; I didn't mean to be patronizing.
No problem--you weren't patronizing at all, and I did tell you I was new at
this!
> With respect to you
- Original Message -
From: "Emily Lena Jones" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, October 28, 2004 4:05 PM
Subject: many-to-many query
> Hi, I'm totally new at this so have no idea whether I'm asking for
something
> easy o
http://dev.mysql.com/doc/mysql/en/JOIN.html
-Original Message-
From: Emily Lena Jones [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 28, 2004 1:05 PM
To: [EMAIL PROTECTED]
Subject: many-to-many query
Hi, I'm totally new at this so have no idea whether I'm asking for somethi
Hi, I'm totally new at this so have no idea whether I'm asking for something
easy or quite difficult.
I am working in MySQL 3.23.58/PHP and am trying to construct a rather complex
query. I have three tables: resources (containing resid, descr, title, url),
topicdir (containing topicid, resid) and
