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 <sangpr...@gmail.com> 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/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
