Re: How to fetch rows with multiple values

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

Result:
3
2
1

?

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