Re: Find ids that are NOT in other two tables.

2006-05-22 Thread Jay Pipes 

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]

Re: Find ids that are NOT in other two tables.

2006-05-22 Thread Ben Liu 

Uh, I don't have anything to add to this thread except that this was
the *exact* problem I was trying to solve and this really helped me a
lot. Thanks guys.

-BL

On 5/22/06, Jay Pipes [EMAIL PROTECTED] wrote:

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


...

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Find ids that are NOT in other two tables.

2006-05-19 Thread Yesmin Patwary 
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\
   
  Thanks in advance for any help.


-
How low will we go? Check out Yahoo! Messenger’s low  PC-to-Phone call rates.

Re: Find ids that are NOT in other two tables.

2006-05-19 Thread Jay Pipes 

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]

Re: Find ids that are NOT in other two tables.

2006-05-19 Thread Yesmin Patwary 
  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