Re: How to fetch rows with multiple values

on 1/20/06 9:08 AM, trepca(at)gmail(dot)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(at)pcisys(dot)net> wrote:
>>
>> on 1/20/06 6:19 AM, trepca(at)gmail(dot)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"