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)
