Thanks, but I don't see how it will work. Am I missing something? On Tue, Jun 7, 2011 at 5:24 PM, Willy Mularto <[email protected]> wrote: > 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/[email protected] >> > > Willy Mularto > F300HD+MR18DE (NLC1725) > > > > > > > >
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[email protected]
