query with an AND condition over a same column

Hello,

I would need to query with an AND condition over a same column.

I have a table like this:

LANGUAGES(
ID varchar(100) NOT NULL,
PERSON_ID varchar(50),
NAME varchar(50),
LEVEL varchar(50))

Here PERSON_ID is a FOREIGN_KEY to the table "PEOPLE"

Some sample data for table LANGUAGES is:

'111', 'people one', 'english', 'high'
'222', 'people one', 'spanish', 'medium'
'333', 'people two', 'english', 'high'
'444', 'people three', 'spanish', 'low'

I want to make a query to filter out people speaking BOTH english AND
spanish

SELECT
*
FROM
people p
LEFT OUTER JOIN
languages lang
ON (p.id = lang.person_id)
WHERE
(lang.NAME = 'english' AND lang.NAME = 'spanish')


But this does return NO results!

What is the correct way to make such queries ?

thank you much

nntp.aioe.org wrote:
> Hello,
>
> I would need to query with an AND condition over a same column.
>
> I have a table like this:
>
> LANGUAGES(
> ID varchar(100) NOT NULL,
> PERSON_ID varchar(50),
> NAME varchar(50),
> LEVEL varchar(50))
>
> Here PERSON_ID is a FOREIGN_KEY to the table "PEOPLE"
>
> Some sample data for table LANGUAGES is:
>
> '111', 'people one', 'english', 'high'
> '222', 'people one', 'spanish', 'medium'
> '333', 'people two', 'english', 'high'
> '444', 'people three', 'spanish', 'low'
>
> I want to make a query to filter out people speaking BOTH english AND
> spanish
>
> SELECT
> *
> FROM
> people p
> LEFT OUTER JOIN
> languages lang
> ON (p.id = lang.person_id)
> WHERE
> (lang.NAME = 'english' AND lang.NAME = 'spanish')
>
>
> But this does return NO results!
>
> What is the correct way to make such queries ?
>
> thank you much

You need to join the languages table to itself:

SELECT
person_id
FROM languages lang1
JOIN languages lang2 ON lang1.person_id = lang2.person_id
WHERE lang1.name = 'english' AND lang2.name = 'spanish'

I leave it as an exercise for you to add the join to the people table

> You need to join the languages table to itself:

thank you :-)

>I would need to query with an AND condition over a same column.

Same column, but DIFFERENT row, right?

>I have a table like this:
>
>LANGUAGES(
> ID varchar(100) NOT NULL,
> PERSON_ID varchar(50),
> NAME varchar(50),
> LEVEL varchar(50))
>
>Here PERSON_ID is a FOREIGN_KEY to the table "PEOPLE"
>
>Some sample data for table LANGUAGES is:
>
>'111', 'people one', 'english', 'high'
>'222', 'people one', 'spanish', 'medium'
>'333', 'people two', 'english', 'high'
>'444', 'people three', 'spanish', 'low'
>
>I want to make a query to filter out people speaking BOTH english AND
>spanish
>
>SELECT
>*
>FROM
>people p
>LEFT OUTER JOIN
>languages lang
>ON (p.id = lang.person_id)
>WHERE
>(lang.NAME = 'english' AND lang.NAME = 'spanish')

Join with 'languages' TWICE. You'll need aliases to distinguish the
two instances. Below l1 is the record for english and l2 is the
record for spanish.

SELECT ...whatever... FROM people p, languages l1, languages l2
WHERE p.id = l1.person_id AND p.id = l2.person_id AND
l1.NAME = 'english' AND l2.NAME = 'spanish';

>But this does return NO results!

The same row can't have different values in the same field.

Gordon Burditt wrote:
>>I would need to query with an AND condition over a same column.
>
> Same column, but DIFFERENT row, right?
>
>>I have a table like this:
>>
>>LANGUAGES(
>> ID varchar(100) NOT NULL,
>> PERSON_ID varchar(50),
>> NAME varchar(50),
>> LEVEL varchar(50))
>>
>>Here PERSON_ID is a FOREIGN_KEY to the table "PEOPLE"
>>
>>Some sample data for table LANGUAGES is:
>>
>>'111', 'people one', 'english', 'high'
>>'222', 'people one', 'spanish', 'medium'
>>'333', 'people two', 'english', 'high'
>>'444', 'people three', 'spanish', 'low'
>>
>>I want to make a query to filter out people speaking BOTH english AND
>>spanish
>>
>>SELECT
>>*
>>FROM
>>people p
>>LEFT OUTER JOIN
>>languages lang
>>ON (p.id = lang.person_id)
>>WHERE
>>(lang.NAME = 'english' AND lang.NAME = 'spanish')
>
> Join with 'languages' TWICE. You'll need aliases to distinguish the
> two instances. Below l1 is the record for english and l2 is the
> record for spanish.
>
> SELECT ...whatever... FROM people p, languages l1, languages l2
> WHERE p.id = l1.person_id AND p.id = l2.person_id AND
> l1.NAME = 'english' AND l2.NAME = 'spanish';
>
>>But this does return NO results!
>
> The same row can't have different values in the same field.

It is far clearer to use the explicit JOIN syntax that I posted about 6
hours ago.