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 <sangprabv@stripped> 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=1
>>
>
> Willy Mularto
> F300HD+MR18DE (NLC1725)
>
>
>
>
>
>
>
>
