Hi,
Greatly appreciated your kind help. Would it work, if I rewrite your query
as below to remove all records from company_db that don't have any relation to
other two tables?
DELETE
FROM company_db c
LEFT JOIN person p
ON c.company_id = p.company_id
LEFT JOIN customer cu
ON c.company_id = cu.company_id
WHERE p.company_id IS NULL
AND cu.company_id IS NULL;
I am not sure if this join query will remove records from other tables.
Jay Pipes <[EMAIL PROTECTED]> wrote:
Yesmin Patwary wrote:
> Dear All,
>
> Need to find ids that are NOT in other two tables. With the help of PHP
> scripting and three queries I am able to do the task. Can this be done with
> one mysql query?
>
> Below are current quires:
>
> SELECT DISTINCT company_id FROM company_db
> SELECT company_id FROM person WHERE company_id=\"$company_id\"
> SELECT company_id FROM customer WHERE company_id=\"$company_id\"
>
Use outer joins:
SELECT DISTINCT c.company_id
FROM company_db c
LEFT JOIN person p
ON c.company_id = p.company_id
LEFT JOIN customer cu
ON c.company_id = cu.company_id
WHERE p.company_id IS NULL
AMD cu.company_id IS NULL;
--
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Roaming North America, based in Columbus, Ohio
email: [EMAIL PROTECTED] mob: +1 614 406 1267
Are You MySQL Certified? http://www.mysql.com/certification
Got Cluster? http://www.mysql.com/cluster
---------------------------------
Feel free to call! Free PC-to-PC calls. Low rates on PC-to-Phone. Get Yahoo!
Messenger with Voice
