[PHP-DB] How to exclude the result of union of two tables from the result of union of other two tables
I have a php page where I would like to list a numer of companies as below: 1- retrieve all distinct company names from TableA (SELECT company_name FROM TableA) 2- retrieve all distinct company names from TableB 3- combine the two lists as list1 4- retrieve all distinct company names from TableC 5- retrieve all distinct company names from TableD 6- combine the two lists as list2 7- display company names in list1 that don't exist in list2 MySql version is 3.23.56 3.23.56 , so I cannot use UNION. I would like to accomplish this with as little php code as possible, using mostly SQL. Thanks, Oz. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] How to exclude the result of union of two tables from the result of union of other two tables
Oz wrote: I have a php page where I would like to list a numer of companies as below: 1- retrieve all distinct company names from TableA (SELECT company_name FROM TableA) CREATE TEMPORARY TABLE temp1 SELECT DISTINCT(company_name) FROM TableA 2- retrieve all distinct company names from TableB 3- combine the two lists as list1 INSERT INTO temp1 SELECT DISTINCT(company_name) FROM TableB 4- retrieve all distinct company names from TableC CREATE TEMPORARY TABLE temp2 SELECT DISTINCT(company_name) FROM TableC 5- retrieve all distinct company names from TableD 6- combine the two lists as list2 INSERT INTO temp2 SELECT DISTINCT(company_name) FROM TableD 7- display company names in list1 that don't exist in list2 SELECT t1.company_name FROM temp1 t1 LEFT JOIN temp2 t2 ON t1.company_name = t2.company_name WHERE t2.company_name IS NULL MySql version is 3.23.56 3.23.56 , so I cannot use UNION. I would like to accomplish this with as little php code as possible, using mostly SQL. How about using all SQL? :) All of this is in the MySQL manual, btw... -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] How to exclude the result of union of two tables from the result of union of other two tables
This can be done in one query using subselects. El Vie 05 Sep 2003 15:49, John W. Holmes escribi: Oz wrote: I have a php page where I would like to list a numer of companies as below: 1- retrieve all distinct company names from TableA (SELECT company_name FROM TableA) CREATE TEMPORARY TABLE temp1 SELECT DISTINCT(company_name) FROM TableA 2- retrieve all distinct company names from TableB 3- combine the two lists as list1 INSERT INTO temp1 SELECT DISTINCT(company_name) FROM TableB 4- retrieve all distinct company names from TableC CREATE TEMPORARY TABLE temp2 SELECT DISTINCT(company_name) FROM TableC 5- retrieve all distinct company names from TableD 6- combine the two lists as list2 INSERT INTO temp2 SELECT DISTINCT(company_name) FROM TableD 7- display company names in list1 that don't exist in list2 SELECT t1.company_name FROM temp1 t1 LEFT JOIN temp2 t2 ON t1.company_name = t2.company_name WHERE t2.company_name IS NULL MySql version is 3.23.56 3.23.56 , so I cannot use UNION. I would like to accomplish this with as little php code as possible, using mostly SQL. How about using all SQL? :) All of this is in the MySQL manual, btw... -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- Porqu usar una base de datos relacional cualquiera, si pods usar PostgreSQL? - Martn Marqus |[EMAIL PROTECTED] Programador, Administrador, DBA | Centro de Telematica Universidad Nacional del Litoral - -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
