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