Thread: 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
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
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, Michael Glaesemann <grzm@myrealbox.com> wrote:
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
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
Thanks anyway :)
Sebastjan
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
Result:
3
2
1
?
Keary Suska
Esoteritech, Inc.
"Demystifying technology for your home or business"
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
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"