IN clause

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=gcdmg-mysql-2@m.gmane.org

Re: IN clause

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=sangprabv@gmail.com
>

Willy Mularto
F300HD+MR18DE (NLC1725)








--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: IN clause

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 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)
>
>
>
>
>
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: IN clause

ok. here's what I am trying now. First create a table with the code
below with a list of countries that have the two person names I want.
CREATE TABLE `table_new` SELECT t1.country
FROM `table_old` as t1
JOIN
`table_old` as t2
USING(country)
JOIN
`table_old` as t3
USING(country)
WHERE (t2.`person_name` ='Tom' )
AND (t3.`person_name` ='Kevin' )
GROUP BY (country)

In the next step LEFT JOIN this to the original table USING(country).
I guess this would work.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: IN clause

>>>> 2011/06/07 17:06 +0200, joe j >>>>
WHERE (`person_name` ='Tom' AND `person_name` ='Kevin' )
<<<<<<<<
This is quite wrong: it is always false. Try another operator.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org