pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSimple Query?
12+ messages / 8 participants
[nested] [flat]
* Simple Query?
@ 2007-09-11 21:27 Koen Bok <koen@madebysofa.com>
0 siblings, 3 replies; 12+ messages in thread
From: Koen Bok @ 2007-09-11 21:27 UTC (permalink / raw)
To: pgsql-sql
I am doing some optimization on our search, but I need some advise...
table: item
id name
--------------------------------------
1 iPod
2 Zune
3 Walkman
table: search_item
id_search id_item
--------------------------------------
1 1
1 2
1 3
2 2
2 3
3 1
3 3
Now what I want to have is the items that match with id_search 1 and
2 and 3. Therefore I use the following SQL query.
SELECT * FROM item WHERE id IN
(SELECT id_item FROM search_item WHERE id_search=1 AND id_item IN
(SELECT id_item FROM search_item WHERE id_search=2 AND id_item IN
(SELECT id_item FROM search_item WHERE id_search=3)));
This should only return id_item 3. Would this be the best SQL query
to get this result? I have the feeling there should be something
better, but I cannot find it. Anyone has a hint?
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Simple Query?
@ 2007-09-11 21:46 =?UTF-8?Q?Rodrigo_De_Le=C3=B3n?= <rdeleonp@gmail.com>
parent: Koen Bok <koen@madebysofa.com>
2 siblings, 0 replies; 12+ messages in thread
From: Rodrigo De León @ 2007-09-11 21:46 UTC (permalink / raw)
To: Koen Bok <koen@madebysofa.com>; +Cc: pgsql-sql
On 9/11/07, Koen Bok <koen@madebysofa.com> wrote:
> Anyone has a hint?
Another way:
SELECT i.*
FROM item i JOIN search_item s1 ON i.ID = s1.id_item
JOIN search_item s2 ON s1.id_item = s2.id_item
JOIN search_item s3 ON s2.id_item = s3.id_item
WHERE s1.id_search = 1
AND s2.id_search = 2
AND s3.id_search = 3
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Simple Query?
@ 2007-09-11 21:59 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Koen Bok <koen@madebysofa.com>
2 siblings, 0 replies; 12+ messages in thread
From: Richard Broersma Jr @ 2007-09-11 21:59 UTC (permalink / raw)
To: Koen Bok <koen@madebysofa.com>; pgsql-sql
--- Koen Bok <koen@madebysofa.com> wrote:
> I am doing some optimization on our search, but I need some advise...
>
> table: item
>
> id name
> --------------------------------------
> 1 iPod
> 2 Zune
> 3 Walkman
>
> table: search_item
>
> id_search id_item
> --------------------------------------
> 1 1
> 1 2
> 1 3
> 2 2
> 2 3
> 3 1
> 3 3
>
>
> Now what I want to have is the items that match with id_search 1 and
> 2 and 3. Therefore I use the following SQL query.
SELECT id_search
FROM Search_item
GROUP BY id_search
HAVING Count( * ) = 3
AND Max( id_item ) = 3
AND Min( id_item ) = 1;
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Simple Query?
@ 2007-09-12 14:22 Osvaldo Rosario Kussama <osvaldo_kussama@yahoo.com.br>
parent: Koen Bok <koen@madebysofa.com>
2 siblings, 0 replies; 12+ messages in thread
From: Osvaldo Rosario Kussama @ 2007-09-12 14:22 UTC (permalink / raw)
To: Koen Bok <koen@madebysofa.com>; pgsql-sql
Koen Bok escreveu:
> I am doing some optimization on our search, but I need some advise...
>
> table: item
>
> id name
> --------------------------------------
> 1 iPod
> 2 Zune
> 3 Walkman
>
> table: search_item
>
> id_search id_item
> --------------------------------------
> 1 1
> 1 2
> 1 3
> 2 2
> 2 3
> 3 1
> 3 3
>
>
> Now what I want to have is the items that match with id_search 1 and 2
> and 3. Therefore I use the following SQL query.
>
> SELECT * FROM item WHERE id IN
> (SELECT id_item FROM search_item WHERE id_search=1 AND id_item IN
> (SELECT id_item FROM search_item WHERE id_search=2 AND id_item IN
> (SELECT id_item FROM search_item WHERE id_search=3)));
>
> This should only return id_item 3. Would this be the best SQL query to
> get this result? I have the feeling there should be something better,
> but I cannot find it. Anyone has a hint?
>
SELECT * FROM item WHERE id IN
(SELECT id_item FROM search_item WHERE id_search=1
INTERSECT
SELECT id_item FROM search_item WHERE id_search=2
INTERSECT
SELECT id_item FROM search_item WHERE id_search=3);
Osvaldo
^ permalink raw reply [nested|flat] 12+ messages in thread
* simple? query
@ 2009-08-13 11:36 Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
0 siblings, 2 replies; 12+ messages in thread
From: Jan Verheyden @ 2009-08-13 11:36 UTC (permalink / raw)
To: pgsql-sql
Hi,
I was trying to run following query but doesn't work:
if (uid='janvleuven10') then
insert into test (registered) values ('1');
else
insert into test (registered) values ('0');
end if;
[cid:image001.png@01CA1C1B.03410D10]
Anyone an idea?
Regards,
Jan
Attachments:
image001.png (image/png, 5.3K) download
view image
* Re: simple? query
@ 2009-08-13 20:46 Relyea, Mike <Mike.Relyea@xerox.com>
parent: Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
1 sibling, 1 reply; 12+ messages in thread
From: Relyea, Mike @ 2009-08-13 20:46 UTC (permalink / raw)
To: pgsql-sql; +Cc: Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
> From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Jan Verheyden
> Subject: [SQL] simple? query
>
> Hi,
> I was trying to run following query but doesn't work:
> if (uid='janvleuven10') then
> insert into test (registered) values ('1');
> else
> insert into test (registered) values ('0');
> end if;
Perhaps UPDATE is what you're looking for?
http://www.postgresql.org/docs/8.4/static/sql-update.html
UPDATE test SET registered = '1' WHERE uid = 'janvleuven10';
UPDATE test set registered = '0' WHERE uid <> 'janvleuven10';
Mike
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: simple? query
@ 2009-08-14 08:58 Jasen Betts <jasen@xnet.co.nz>
parent: Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
1 sibling, 0 replies; 12+ messages in thread
From: Jasen Betts @ 2009-08-14 08:58 UTC (permalink / raw)
To: pgsql-sql
On 2009-08-13, Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be> wrote:
> --_004_E30C7040DE22624185BAD4093190B54437BE5DB4C1EX2007MBX2uzk_
> Content-Type: multipart/alternative;
> boundary="_000_E30C7040DE22624185BAD4093190B54437BE5DB4C1EX2007MBX2uzk_"
>
> --_000_E30C7040DE22624185BAD4093190B54437BE5DB4C1EX2007MBX2uzk_
> Content-Type: text/plain; charset="us-ascii"
> Content-Transfer-Encoding: quoted-printable
>
> Hi,
>
> I was trying to run following query but doesn't work:
>
> if (uid='janvleuven10') then
> insert into test (registered) values ('1');
> else
> insert into test (registered) values ('0');
> end if;
that's not SQL. (it could be plpgsql)
if you need to do it in SQL do this.
insert into test (registered)
values ( case when uid='janvleuven10' then '1' else '0' end );
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: simple? query
@ 2009-08-14 12:58 Relyea, Mike <Mike.Relyea@xerox.com>
parent: Relyea, Mike <Mike.Relyea@xerox.com>
0 siblings, 1 reply; 12+ messages in thread
From: Relyea, Mike @ 2009-08-14 12:58 UTC (permalink / raw)
To: pgsql-sql; +Cc: Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
> From: Relyea, Mike [mailto:Mike.Relyea@xerox.com]
> Sent: Thursday, August 13, 2009 10:47 PM
>
> > From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Jan Verheyden
> > Subject: [SQL] simple? query
> >
> > Hi,
> > I was trying to run following query but doesn't work:
> > if (uid='janvleuven10') then
> > insert into test (registered) values ('1'); else
> > insert into test (registered) values ('0'); end if;
>
> Perhaps UPDATE is what you're looking for?
> http://www.postgresql.org/docs/8.4/static/sql-update.html
>
> UPDATE test SET registered = '1' WHERE uid = 'janvleuven10';
> UPDATE test set registered = '0' WHERE uid <> 'janvleuven10';
>
>
>
>
> From: Jan Verheyden [mailto:jan.verheyden@uz.kuleuven.ac.be]
> Sent: Friday, August 14, 2009 3:11 AM
> To: Relyea, Mike
> Subject: RE: [SQL] simple? query
>
> Hi,
>
> Thanks for the reply. I was thinking of that, but the problem
> is that if it's not registered, the uid is not in the test database...
> I think in your example all the other rows will be signed as
> 'not registered, is this possible?'
>
> Regards,
>
> Jan
You're right, my suggestion will not insert new records. It will only
update existing ones. And yes, in my example all of the existing rows
where uid is not equal to janvleuven10 will have the registered value
set to 0. That's how I interpreted the example you gave in your
original post.
Mike
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: simple? query
@ 2009-08-14 14:21 Relyea, Mike <Mike.Relyea@xerox.com>
parent: Relyea, Mike <Mike.Relyea@xerox.com>
0 siblings, 1 reply; 12+ messages in thread
From: Relyea, Mike @ 2009-08-14 14:21 UTC (permalink / raw)
To: pgsql-sql; +Cc: Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
> From: Jan Verheyden [mailto:jan.verheyden@uz.kuleuven.ac.be]
> Sent: Friday, August 14, 2009 9:03 AM
> To: Relyea, Mike
> Subject: RE: [SQL] simple? query
>
> The goal is, where uid not equals to 'janvleuven10' a new
> record should be inserted with the uid, and registered=0
>
> Regards,
>
> Jan
So if a record is found you want to update it and if a record isn't
found you want to insert it. I think you'll probably want to use
plpgsql http://www.postgresql.org/docs/8.4/static/plpgsql.html or some
other language like Jasen suggested. I don't know of a way to do this
with straight sql.
Mike
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: simple? query
@ 2009-08-14 14:53 Tim Landscheidt <tim@tim-landscheidt.de>
parent: Relyea, Mike <Mike.Relyea@xerox.com>
0 siblings, 0 replies; 12+ messages in thread
From: Tim Landscheidt @ 2009-08-14 14:53 UTC (permalink / raw)
To: pgsql-sql
"Relyea, Mike" <Mike.Relyea@xerox.com> wrote:
>> The goal is, where uid not equals to 'janvleuven10' a new
>> record should be inserted with the uid, and registered=0
> So if a record is found you want to update it and if a record isn't
> found you want to insert it. I think you'll probably want to use
> plpgsql http://www.postgresql.org/docs/8.4/static/plpgsql.html or some
> other language like Jasen suggested. I don't know of a way to do this
> with straight sql.
Something along the lines of:
| UPDATE table SET attribute = 'something' WHERE primary_key = 'id';
| INSERT INTO table (primary_key, attribute) SELECT 'id', 'something' WHERE 'id' NOT IN (SELECT primary_key FROM table);
should achieve that.
Tim
^ permalink raw reply [nested|flat] 12+ messages in thread
* FW: simple? query
@ 2009-08-18 07:04 Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
0 siblings, 1 reply; 12+ messages in thread
From: Jan Verheyden @ 2009-08-18 07:04 UTC (permalink / raw)
To: pgsql-sql
Hi,
Thanks for the suggestion, the only problem is, if primary key is used then each row should be unique what is not true; since I have a column 'registered' what only can be 1 or 0...
Regards,
Jan
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Tim Landscheidt
Sent: Friday, August 14, 2009 4:53 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] simple? query
"Relyea, Mike" <Mike.Relyea@xerox.com> wrote:
>> The goal is, where uid not equals to 'janvleuven10' a new
>> record should be inserted with the uid, and registered=0
> So if a record is found you want to update it and if a record isn't
> found you want to insert it. I think you'll probably want to use
> plpgsql http://www.postgresql.org/docs/8.4/static/plpgsql.html or some
> other language like Jasen suggested. I don't know of a way to do this
> with straight sql.
Something along the lines of:
| UPDATE table SET attribute = 'something' WHERE primary_key = 'id';
| INSERT INTO table (primary_key, attribute) SELECT 'id', 'something' WHERE 'id' NOT IN (SELECT primary_key FROM table);
should achieve that.
Tim
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: FW: simple? query
@ 2009-08-18 23:39 Tim Landscheidt <tim@tim-landscheidt.de>
parent: Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
0 siblings, 0 replies; 12+ messages in thread
From: Tim Landscheidt @ 2009-08-18 23:39 UTC (permalink / raw)
To: pgsql-sql
Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be> wrote:
> Thanks for the suggestion, the only problem is, if primary key is used then each row should be unique what is not true; since I have a column 'registered' what only can be 1 or 0...
> [...]
I have no idea what you are trying to say.
Tim
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2009-08-18 23:39 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-09-11 21:27 Simple Query? Koen Bok <koen@madebysofa.com>
2007-09-11 21:46 ` =?UTF-8?Q?Rodrigo_De_Le=C3=B3n?= <rdeleonp@gmail.com>
2007-09-11 21:59 ` Richard Broersma Jr <rabroersma@yahoo.com>
2007-09-12 14:22 ` Osvaldo Rosario Kussama <osvaldo_kussama@yahoo.com.br>
2009-08-13 11:36 simple? query Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-13 20:46 ` Re: simple? query Relyea, Mike <Mike.Relyea@xerox.com>
2009-08-14 12:58 ` Re: simple? query Relyea, Mike <Mike.Relyea@xerox.com>
2009-08-14 14:21 ` Re: simple? query Relyea, Mike <Mike.Relyea@xerox.com>
2009-08-14 14:53 ` Re: simple? query Tim Landscheidt <tim@tim-landscheidt.de>
2009-08-14 08:58 ` Re: simple? query Jasen Betts <jasen@xnet.co.nz>
2009-08-18 07:04 FW: simple? query Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-18 23:39 ` Re: FW: simple? query Tim Landscheidt <tim@tim-landscheidt.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox