How to fetch rows with multiple values

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"