Hello,

I'm sure this is very simple and I've probably just got a memory block,
however.

I have two tables, one contains group data (imagine Yahoo! Groups).
Another contains a list of all of the members subscribed to these
groups (these are just references to user ids, and not the actually
member accounts. Therefore, the same userid can be in this second
table, table 2 below, many times, but only with different groupids).

Table 1:
group_id int(10) unsigned (autonumber)
group_ownerid int(10) unsigned
group_name text
group_access enum('Members','Invite Only')

Table 2:
member_id int(10) unsigned (autonumber)
member_groupid int(10) unsigned
member_userid int(10) unsigned

I'm looking to generate a list of groups from table 1, whereby the
logged in user (for arguments' sake, userid=1) is not subscribed to the
respective groups (i.e. their userid is NOT in table 2, for the
respective groupid).

Table 2, as previously stated, contains a list of members in relevant
groups. userid 1 might be in groups 3, 5 and 10, so there will be 3
entries listed:

member_id member_groupid member_userid
1 3 1
2 5 1
3 10 1

Thus, looking at the above data for table 2, if we assume there are 12
groups in table 1, I should be able to generate a list for groups 1, 2,
4, 6, 7, 8, 9, 11 and 12.

At the moment, the SQL query reads:

SELECT * FROM groups_group ORDER BY group_name ASC

Which will clearly return all of the groups from table 1.

Is there anybody who can point me in the right direction to provide the
correct funcionality? I'm pretty sure it's not a join I want, but I
could be wrong?

If any more information is required, please let me know.

Thanks,

Paul

Paul wrote:
> Hello,
>
> I'm sure this is very simple and I've probably just got a memory block,
> however.
>
> I have two tables, one contains group data (imagine Yahoo! Groups).
> Another contains a list of all of the members subscribed to these
> groups (these are just references to user ids, and not the actually
> member accounts. Therefore, the same userid can be in this second
> table, table 2 below, many times, but only with different groupids).
>
> Table 1:
> group_id int(10) unsigned (autonumber)
> group_ownerid int(10) unsigned
> group_name text
> group_access enum('Members','Invite Only')
>
> Table 2:
> member_id int(10) unsigned (autonumber)
> member_groupid int(10) unsigned
> member_userid int(10) unsigned
>
> I'm looking to generate a list of groups from table 1, whereby the
> logged in user (for arguments' sake, userid=1) is not subscribed to the
> respective groups (i.e. their userid is NOT in table 2, for the
> respective groupid).
>
> Table 2, as previously stated, contains a list of members in relevant
> groups. userid 1 might be in groups 3, 5 and 10, so there will be 3
> entries listed:
>
> member_id member_groupid member_userid
> 1 3 1
> 2 5 1
> 3 10 1
>
> Thus, looking at the above data for table 2, if we assume there are 12
> groups in table 1, I should be able to generate a list for groups 1, 2,
> 4, 6, 7, 8, 9, 11 and 12.
>
> At the moment, the SQL query reads:
>
> SELECT * FROM groups_group ORDER BY group_name ASC
>
> Which will clearly return all of the groups from table 1.
>
> Is there anybody who can point me in the right direction to provide the
> correct funcionality? I'm pretty sure it's not a join I want, but I
> could be wrong?
>
> If any more information is required, please let me know.
>
> Thanks,
>
> Paul

have a look at
http://groups.google.co.uk/group/com...ed82ec06a82b18

Paul wrote:
> Hello,
>
> I'm sure this is very simple and I've probably just got a memory
> block, however.
>
> I have two tables, one contains group data (imagine Yahoo! Groups).
> Another contains a list of all of the members subscribed to these
> groups (these are just references to user ids, and not the actually
> member accounts. Therefore, the same userid can be in this second
> table, table 2 below, many times, but only with different groupids).
>
> Table 1:
> group_id int(10) unsigned (autonumber)
> group_ownerid int(10) unsigned
> group_name text
> group_access enum('Members','Invite Only')
>
> Table 2:
> member_id int(10) unsigned (autonumber)
> member_groupid int(10) unsigned
> member_userid int(10) unsigned
>
> I'm looking to generate a list of groups from table 1, whereby the
> logged in user (for arguments' sake, userid=1) is not subscribed to
> the respective groups (i.e. their userid is NOT in table 2, for the
> respective groupid).
>
> Table 2, as previously stated, contains a list of members in relevant
> groups. userid 1 might be in groups 3, 5 and 10, so there will be 3
> entries listed:
>
> member_id member_groupid member_userid
> 1 3 1
> 2 5 1
> 3 10 1
>
> Thus, looking at the above data for table 2, if we assume there are 12
> groups in table 1, I should be able to generate a list for groups 1,
> 2, 4, 6, 7, 8, 9, 11 and 12.
>
> At the moment, the SQL query reads:
>
> SELECT * FROM groups_group ORDER BY group_name ASC
>
> Which will clearly return all of the groups from table 1.
>
> Is there anybody who can point me in the right direction to provide
> the correct funcionality? I'm pretty sure it's not a join I want, but
> I could be wrong?

As strawberry has mentioned you are wrong and it is a join that you need :-)

However a post that is coser to what you want is
http://groups.google.co.uk/group/com...1c73d449abe840

Paul Lautman wrote:
> As strawberry has mentioned you are wrong and it is a join that you need :-)
>
> However a post that is coser to what you want is
> http://groups.google.co.uk/group/com...1c73d449abe840

I looked at the two examples; turns out it was a simple thing after
all! Final working query:

SELECT DISTINCT * FROM groups_group
INNER JOIN accounts_user ON user_id=group_ownerid
LEFT JOIN groups_member ON member_groupid=group_id
WHERE group_access="Members"
AND member_groupid IS NULL
ORDER BY group_name ASC

Thanks for your help; now I can get on with the rest of the project!!