Yesmin Patwary wrote:
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.
The DELETE statement can specify which tables you want to delete from.
Specify the table you want to delete from before the FROM keyword. And,
yes, the query works properly, as the example below indicates :)
[EMAIL PROTECTED]:~$ mysql -uroot -p test
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.18-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.21 sec)
mysql> CREATE TABLE t2 (id INT NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE t3 (id INT NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.05 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t2 VALUES (2),(4),(6),(8),(10);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t3 VALUES (3),(5),(7),(9),(11);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> DELETE t1 FROM t1
-> LEFT JOIN t2 ON t1.id = t2.id
-> LEFT JOIN t3 ON t1.id = t3.id
-> WHERE t2.id IS NULL
-> AND t3.id IS NULL;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM t1;
+----+
| id |
+----+
| 2 |
| 3 |
| 4 |
| 5 |
+----+
4 rows in set (0.00 sec)
mysql> SELECT * FROM t2;
+----+
| id |
+----+
| 2 |
| 4 |
| 6 |
| 8 |
| 10 |
+----+
5 rows in set (0.00 sec)
mysql> SELECT * FROM t3;
+----+
| id |
+----+
| 3 |
| 5 |
| 7 |
| 9 |
| 11 |
+----+
5 rows in set (0.00 sec)
Hope this answers your questions!
Cheers,
-jay
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
