On 25-Jul-2003 Vikram Vaswani wrote:
<snip>
> mysql> SELECT * FROM branches;
> +------+-----+--------------------------------+------+
>| bid | cid | bdesc | bloc |
> +------+-----+--------------------------------+------+
>| 1011 | 101 | Corporate HQ | CA |
>| 1012 | 101 | Accounting Department | NY |
>| 1013 | 101 | Customer Grievances Department | KA |
>| 1041 | 104 | Branch Office (East) | MA |
>| 1042 | 104 | Branch Office (West) | CA |
>| 1101 | 110 | Head Office | CA |
>| 1031 | 103 | N Region HO | ME |
>| 1032 | 103 | NE Region HO | CT |
>| 1033 | 103 | NW Region HO | NY |
> +------+-----+--------------------------------+------+
> 9 rows in set (0.01 sec)
>
> I need to delete all clients with no branches. I need to use a subquery
> to
> do this. Given these constraints, I came up with the following:
>
> mysql> delete from clients where cid = (select clients.cid from clients
> left join branches using (cid) WHERE bid is null);
>
<snip>
Un-tested:
DELETE FROM clients WHERE cid NOT IN
(SELECT b1.cid FROM branches AS b1, branches AS b2
WHERE b1.cid=b2.cid and b1.bid != b2.bid);
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
