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.
