From: joe j
Date: June 7 2011 3:31pm
Subject: Re: IN clause
List-Archive: http://lists.mysql.com/mysql/225203
Message-Id: <BANLkTim2tn5qi48-Fa8kEO++ixixyB5RTw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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 =A0two 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" =A0 "person name"
>> US =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Antony
>> US =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Tom
>> US =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Jack
>> US =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Kevin
>>
>> China =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Kevin
>> China =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Tom
>> China =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Ann
>> China =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Mike
>>
>> UK =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Kevin
>> UK =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Mike
>> UK =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Jack
>> UK =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Beyer
>>
>>
>> I want to have the following (the two countries that have person names
>> Tom and Kevin):
>> "country" =A0 "person_name"
>> US =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Antony
>> US =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Tom
>> US =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Jack
>> US =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Kevin
>>
>> China =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Kevin
>> China =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Tom
>> China =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Ann
>> China =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Mike
>>
>> I tried the following but obviously that didn't work.
>>
>> CREATE TABLE `table_new` SELECT *
>> FROM `table_old`
>> WHERE (`person_name` =3D'Tom' AND `person_name` =3D'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: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dsangprabv@gm=
ail.com
>>
>
> Willy Mularto
> F300HD+MR18DE (NLC1725)
>
>
>
>
>
>
>
>