List:General Discussion« Previous MessageNext Message »
From:joe j Date:June 7 2011 3:06pm
Subject:IN clause
View as plain text  
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.
Thread
IN clausejoe j7 Jun
  • Re: IN clauseWilly Mularto7 Jun
    • Re: IN clausejoe j7 Jun
      • Re: IN clausejoe j7 Jun
  • Re: IN clausehsv9 Jun