Dear all, I wish to create a new table from a table that has two columns "country" and "person_name". Thus from the table below, I'd like to select all the records of those countries that have person names 'Tom' and 'Kevin'.
"country" "person name" US Antony US Tom US Jack US Kevin China Kevin China Tom China Ann China Mike UK Kevin UK Mike UK Jack UK Beyer I want to have the following (the two countries that have person names Tom and Kevin): "country" "person_name" US Antony US Tom US Jack US Kevin China Kevin China Tom China Ann China Mike I tried the following but obviously that didn't work. CREATE TABLE `table_new` SELECT * FROM `table_old` WHERE (`person_name` ='Tom' AND `person_name` ='Kevin' ) AND ((table_old.country) IN (SELECT DISTINCT (table_old2.`country) FROM table_old AS table_old2)); I know I can do this by creating two additional tables, but was wondering if there was a direct way. Best, Joe. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
