Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2006
    Posts
    2

    Unanswered: Join to find rows not in second table

    Hi,

    I'm trying to write a SELECT for MySQL 4.0 using a JOIN. I can get it to work in v4.1 using a subquery, but my ISP provides v4.0 only.

    I've got 2 tables:
    - group: Describes groups that exist, key is group_id
    - usergroup: Members of groups - has user_id and group_id

    I want to find which groups a user **doesn't** belong to (say user_id=3).

    In MySQL 4.1+ I can do this using a subquery:

    SELECT group_id FROM group
    WHERE group_id NOT IN (
    SELECT group_id
    FROM group g,usergroup ug
    WHERE ug.group_id = g.group_id
    AND ug.user_id = 3
    )

    This query doesn't work in MySQL 4.0, no I need to use JOIN (I think). I've been searching forums and trying things out, but I cannot figure out how to make it work.

    Can anyone help please??

  2. #2
    Join Date
    Jun 2005
    Posts
    23
    You need a left join to do that
    without a subquery:

    Code:
    SELECT group_id
    FROM group g
    LEFT JOIN usergroup u
    ON g.group_id=u.group_id
    AND u.user_id=3
    WHERE u.user_id IS NULL

  3. #3
    Join Date
    Jun 2005
    Posts
    23
    Quote Originally Posted by snoop
    Hi,
    I want to find which groups a user **doesn't** belong to (say user_id=3).

    In MySQL 4.1+ I can do this using a subquery:

    SELECT group_id FROM group
    WHERE group_id NOT IN (
    SELECT group_id
    FROM group g,usergroup ug
    WHERE ug.group_id = g.group_id
    AND ug.user_id = 3
    )

    Additionally, in the above query you don't need the join:
    Code:
    SELECT group_id FROM group
    WHERE group_id NOT IN (
    SELECT group_id FROM usergroup WHERE user_id=3 GROUP BY group_id
    )

  4. #4
    Join Date
    Jan 2006
    Posts
    2
    Excellent stuff, works perfectly. Appreciate the help.

Posting Permissions

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