List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 28 1999 11:53am
Subject:Re: Use of JOIN.
View as plain text  
On Tue, 1999-09-28 11:34:27 +0100, Thomas Barrett wrote:
> Table1: USERS
> +----+---------+----------+
> | ID | name    | password |
> +----+---------+----------+
> | 1  | tom1    | lemon    |
> | 2  | tom2    | apple    |
> | 3  | tom3    | apricot  |
> | 4  | tom4    | melon    |
> +----+---------+----------+
> 
> Table2: USERSINGROUPS
> +--------+---------+
> | userID | groupID |
> +--------+---------+
> |   1    |       1 |
> |   2    |       1 |
> |   3    |       2 |
> |   4    |       2 |
> +--------+---------+
> 
> Assume the tables above are complete and I proceed to:
> 
> mysql> DELETE from USERS where name="tom4";
> 
> How would I go about finding all entries in USERSINGROUPS that are
> not in USERS
> (In the case above it would be the last entry in USERSINGROUPS)?

In ANSI SQL using sub-selects, you'd write:
  SELECT userID
  FROM   USERSINGROUPS
  WHERE  userID NOT IN (
            SELECT ID
            FROM USERS
         )
  ;

As MySQL doesn't support sub-selects yet, the work-around is to use
  USERSINGROUPS LEFT JOIN USERS ON USERSINGROUPS.userID=USERS.ID
so for every row in USERSINGROUPS which has _no_ matching counterpart
in USERS, the fields of USERS will have NULL values.  And this is
something we can test for:

  SELECT uig.userID AS ID
  FROM   USERSINGROUPS AS uig LEFT JOIN USERS AS u
         ON uig.userID = u.ID
  WHERE  u.ID IS NULL;

Now just store the list of ids, which you get from this query ...

> And how would I delete them from USERSINGROUPS?

... and use them to build a new query:

  DELETE FROM USERSINGROUPS WHERE userID IN ('id1','id2',...);

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Use of JOIN.Thomas Barrett28 Sep
  • Re: Use of JOIN.Martin Ramsch28 Sep
    • RE: Use of JOIN.Thomas Barrett28 Sep
      • Re: Use of JOIN.Martin Ramsch28 Sep