Professional Web Applications Themes

Select records from one table where a key doesn't exist in another - MySQL

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 ...

  1. #1

    Default Select records from one table where a key doesn't exist in another

    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 Guest

  2. #2

    Default Re: Select records from one table where a key doesn't exist in another


    Paul wrote: 

    have a look at
    http://groups.google.co.uk/group/comp.databases.mysql/browse_thread/thread/3ded82ec06a82b18

    strawberry Guest

  3. #3

    Default Re: Select records from one table where a key doesn't exist in another

    Paul 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/comp.databases.mysql/browse_frm/thread/4e1c73d449abe840


    Paul Guest

  4. #4

    Default Re: Select records from one table where a key doesn't exist in another


    Paul Lautman wrote: 

    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!!

    Paul Guest

Similar Threads

  1. Replies: 0
    Last Post: September 8th, 08:49 PM
  2. [mysql] Table Doesn't Exist
    By khamstra in forum Coldfusion Database Access
    Replies: 7
    Last Post: July 21st, 08:10 PM
  3. Replies: 1
    Last Post: July 29th, 04:31 PM
  4. A2k: data entry subform for records that don't exist
    By lfaherty in forum Microsoft Access
    Replies: 1
    Last Post: July 22nd, 12:32 PM
  5. Replies: 3
    Last Post: July 17th, 01:13 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139