Why not GROUP BY?
On Jun 7, 2011, at 10:06 PM, joe j wrote: > 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=sangpr...@gmail.com > Willy Mularto F300HD+MR18DE (NLC1725) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
