Обсуждение: How to fetch rows with multiple values

Hi,

I have a table like this:

CREATE TABLE customer_mapping
(
  "Name" varchar(128) NOT NULL,
  "ID" int8 NOT NULL
)

Data looks something like this:

"john"     1
"peter"    1
"test"      2
"george"  3

What I would like is to write a query where I can specify multiple names and get the IDs which have them.

For now it seems the most efficient way is to use INTERSECT statement:

SELECT "ID" from customer_mapping WHERE "Name"='john'
INTERSECT
SELECT "ID" from customer_mapping WHERE "Name"='peter'

Although, I don't know how exactly to use ORDER, OFFSET and LIMIT in this case...

Anyway, is there any better way of doing this? (I can't change the table structure.)

Thanks, Sebastjan

On Jan 20, 2006, at 22:19 , Sebastjan Trepca wrote:

> What I would like is to write a query where I can specify multiple
> names and get the IDs which have them.
>
> For now it seems the most efficient way is to use INTERSECT statement:
>
> SELECT "ID" from customer_mapping WHERE "Name"='john'
> INTERSECT
> SELECT "ID" from customer_mapping WHERE "Name"='peter'

My first thought is to use a join. Does this do what you want?

select id
from customer_mapping cm1
join customer_mapping cm2 using ("ID")
where cm1."Name" = 'john
and cm2."Name" = 'peter';

> Although, I don't know how exactly to use ORDER, OFFSET and LIMIT
> in this case...

ORDER, OFFSET and LIMIT should work just fine with the JOIN query.
You could also use your intersect in a subquery and then use ORDER,
OFFSET and LIMIT on the outer query, e.g.,

select *
from (
    select "ID"...
    intersect
    select "ID" ...
) as common_names
...

Michael Glaesemann
grzm myrealbox com

Wow, this joined query is super faster then intersect(10x), thanks a lot!!

Regarding that I have to make a join for every term, I would think it would be more consuming. Is there any limit of joins or something similar which I should be aware of?

Sebastjan

on 1/20/06 6:19 AM, trepca@gmail.com purportedly said:

> I have a table like this:
>
> CREATE TABLE customer_mapping
> (
> "Name" varchar(128) NOT NULL,
> "ID" int8 NOT NULL
> )
>
> Data looks something like this:
>
> "john"     1
> "peter"    1
> "test"      2
> "george"  3
>
> What I would like is to write a query where I can specify multiple names and
> get the IDs which have them.
>
> For now it seems the most efficient way is to use INTERSECT statement:
>
> SELECT "ID" from customer_mapping WHERE "Name"='john'
> INTERSECT
> SELECT "ID" from customer_mapping WHERE "Name"='peter'
>
> Although, I don't know how exactly to use ORDER, OFFSET and LIMIT in this
> case...
>
> Anyway, is there any better way of doing this? (I can't change the table
> structure.)

Maybe I'm a little thick this morning but can't you just do:

SELECT "ID" from customer_mapping WHERE "Name"='john' OR "Name"='peter' OR
"Name"='george' ORDER BY "ID" DESC

Result:
3
2
1

?

Keary Suska
Esoteritech, Inc.
"Demystifying technology for your home or business"

No, because I need AND operator between the terms.

Thanks anyway :)

Sebastjan

Keary Suska wrote:
>> Data looks something like this:
>>
>> "john"     1
>> "peter"    1

>
> Maybe I'm a little thick this morning but can't you just do:
>
> SELECT "ID" from customer_mapping WHERE "Name"='john' OR "Name"='peter' OR
> "Name"='george' ORDER BY "ID" DESC

Not quite. He's after ID that have *both* names, so ID=1 above because
it has "john" AND "peter".

--
   Richard Huxton
   Archonet Ltd

on 1/20/06 9:08 AM, trepca@gmail.com purportedly said:

> No, because I need AND operator between the terms.
>
> Thanks anyway :)

Got it. Being thick. Just so I can save face, it may be more efficient to
do:

SELECT (min("ID") = avg("ID)) AS result, min("ID") as "ID" FROM
customer_mapping WHERE "Name"='john' or "Name"='peter'

This only works for one "set", but you can chain them in various ways to get
multiple results. Your app would have to check the result though.

> On 1/20/06, Keary Suska <hierophant@pcisys.net> wrote:
>>
>> on 1/20/06 6:19 AM, trepca@gmail.com purportedly said:
>>
>>> I have a table like this:
>>>
>>> CREATE TABLE customer_mapping
>>> (
>>> "Name" varchar(128) NOT NULL,
>>> "ID" int8 NOT NULL
>>> )
>>>
>>> Data looks something like this:
>>>
>>> "john"     1
>>> "peter"    1
>>> "test"      2
>>> "george"  3
>>>
>>> What I would like is to write a query where I can specify multiple names
>> and
>>> get the IDs which have them.
>>>
>>> For now it seems the most efficient way is to use INTERSECT statement:
>>>
>>> SELECT "ID" from customer_mapping WHERE "Name"='john'
>>> INTERSECT
>>> SELECT "ID" from customer_mapping WHERE "Name"='peter'
>>>
>>> Although, I don't know how exactly to use ORDER, OFFSET and LIMIT in
>> this
>>> case...
>>>
>>> Anyway, is there any better way of doing this? (I can't change the table
>>> structure.)
>>
>> Maybe I'm a little thick this morning but can't you just do:
>>
>> SELECT "ID" from customer_mapping WHERE "Name"='john' OR "Name"='peter' OR
>> "Name"='george' ORDER BY "ID" DESC


Keary Suska
Esoteritech, Inc.
"Demystifying technology for your home or business"