pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp writing a piece of SQL
6+ messages / 3 participants
[nested] [flat]
* Help writing a piece of SQL
@ 2006-02-03 10:43 Nigel Bishop <Nigel.Bishop@ioko.com>
0 siblings, 2 replies; 6+ messages in thread
From: Nigel Bishop @ 2006-02-03 10:43 UTC (permalink / raw)
To: pgsql-sql
Hi, I would appreciate some help writing a piece of SQL
PG803
My table/data looks like this:
username | domain | sendto
+-------------+-------------------------------+-------------------------
-----
Postmaster | intthit08.uk.rabbit.com | root
root | intthit08.uk.rabbit.com | is-unix@rabbit.com
stoat.griffin | trusting.co.uk | stoat.griffin@rabbit.com
stoat.griffin | trusting.com | stoat.griffin@rabbit.com
stoat.griffin | rusty.co.uk | stoat.griffin@rabbit.com
stoat.griffin | rusty.com | stoat.griffin@rabbit.com
matilda.clematis | trusting.com |
matilda.clematis@rabbit.com
matilda.clematis | trusting.co.uk |
matilda.clematis@rabbit.com
matilda.clematis | rusty.co.uk |
matilda.clematis@rabbit.com
matilda.clematis | rusty.com |
matilda.clematis@rabbit.com
* | trusting.com | rusty@rabbit.com
* | trusting.co.uk | rusty@rabbit.com
* | rusty.co.uk | rusty@rabbit.com
* | rusty.com | rusty@rabbit.com
* | windoze.com | windoze@badger.com
* | windoze.co.uk | windoze@badger.com
admin | windoze.co.uk |
matilda.clematis@rabbit.com
admin | windoze.com |
matilda.clematis@rabbit.com
* | saxon.co.uk | superR@uk.diamond.com
* | saxon.com | superR@uk.diamond.com
The query will have the username and domain passed in as variables.
If the username and domain exist then return the sendto
The bit I'm struggling with is if the username doesn't exist then return
the sendto where the domain exists
e.g. username=fred (this doesn't exist) and domain=rusty.com then
return rusty@rabbit.com, matilda.clematis@rabbit.com,
stoat.griffin@rabbit.com
Any help on this would very much appreciated; it's been driving me mad
for the last day.
Thanks
Nigel
Communications on or through ioko's computer systems may be monitored or recorded to secure effective system operation and for other lawful purposes.
Unless otherwise agreed expressly in writing, this communication is to be treated as confidential and the information in it may not be used or disclosed except for the purpose for which it has been sent. If you have reason to believe that you are not the intended recipient of this communication, please contact the sender immediately. No employee is authorised to conclude any binding agreement on behalf of ioko with another party by e-mail without prior express written confirmation.
ioko365 Ltd. VAT reg 656 2443 31. Reg no 3048367. All rights reserved.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help writing a piece of SQL
@ 2006-02-03 11:01 Richard Huxton <dev@archonet.com>
parent: Nigel Bishop <Nigel.Bishop@ioko.com>
1 sibling, 0 replies; 6+ messages in thread
From: Richard Huxton @ 2006-02-03 11:01 UTC (permalink / raw)
To: Nigel Bishop <Nigel.Bishop@ioko.com>; +Cc: pgsql-sql
Nigel Bishop wrote:
>
> username | domain | sendto
> +-------------+-------------------------------+-------------------------
> -----
>
> Postmaster | intthit08.uk.rabbit.com | root
> root | intthit08.uk.rabbit.com | is-unix@rabbit.com
> stoat.griffin | trusting.co.uk | stoat.griffin@rabbit.com
> stoat.griffin | trusting.com | stoat.griffin@rabbit.com
> stoat.griffin | rusty.co.uk | stoat.griffin@rabbit.com
> stoat.griffin | rusty.com | stoat.griffin@rabbit.com
> * | trusting.com | rusty@rabbit.com
> * | trusting.co.uk | rusty@rabbit.com
> * | rusty.co.uk | rusty@rabbit.com
> * | rusty.com | rusty@rabbit.com
> The query will have the username and domain passed in as variables.
> If the username and domain exist then return the sendto
> The bit I'm struggling with is if the username doesn't exist then return
> the sendto where the domain exists
>
> e.g. username=fred (this doesn't exist) and domain=rusty.com then
> return rusty@rabbit.com, matilda.clematis@rabbit.com,
> stoat.griffin@rabbit.com
So: if the username doesn't exist then you return ALL rows with a
matching domain? Not just username="*"?
I'm guessing I've mis-understood and you just want username="*", which
would be something like this:
SELECT sendto, 1 AS priority FROM tbl WHERE username=$1 AND domain=$2
UNION ALL
SELECT sendto, 2 AS priority FROM tbl WHERE username='*' AND domain=$2
ORDER BY priority
LIMIT 1;
Does that help at all?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help writing a piece of SQL
@ 2006-02-03 11:10 Nigel Bishop <Nigel.Bishop@ioko.com>
0 siblings, 0 replies; 6+ messages in thread
From: Nigel Bishop @ 2006-02-03 11:10 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
Thanks for that Richard, it's almost what I'm after:
So where the un and dm exist then return 1 row
Where the un doestn't exist and dm does then return all sendtos' where
the dm exists not just where the un='*'
I hope that makes sense.
Thanks for you help
Nigel Bishop
ioko
T: +44 (0)1904 435 458
M: +44 (0)7881 624 386
F: +44 (0)1904 435 450
E: nigel.bishop@ioko.com
W: www.ioko.com
-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: 03 February 2006 11:02
To: Nigel Bishop
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Help writing a piece of SQL
Nigel Bishop wrote:
>
> username | domain | sendto
>
+-------------+-------------------------------+-------------------------
> -----
>
> Postmaster | intthit08.uk.rabbit.com | root
> root | intthit08.uk.rabbit.com | is-unix@rabbit.com
> stoat.griffin | trusting.co.uk |
stoat.griffin@rabbit.com
> stoat.griffin | trusting.com |
stoat.griffin@rabbit.com
> stoat.griffin | rusty.co.uk |
stoat.griffin@rabbit.com
> stoat.griffin | rusty.com |
stoat.griffin@rabbit.com
> * | trusting.com | rusty@rabbit.com
> * | trusting.co.uk | rusty@rabbit.com
> * | rusty.co.uk | rusty@rabbit.com
> * | rusty.com | rusty@rabbit.com
> The query will have the username and domain passed in as variables.
> If the username and domain exist then return the sendto
> The bit I'm struggling with is if the username doesn't exist then
return
> the sendto where the domain exists
>
> e.g. username=fred (this doesn't exist) and domain=rusty.com then
> return rusty@rabbit.com, matilda.clematis@rabbit.com,
> stoat.griffin@rabbit.com
So: if the username doesn't exist then you return ALL rows with a
matching domain? Not just username="*"?
I'm guessing I've mis-understood and you just want username="*", which
would be something like this:
SELECT sendto, 1 AS priority FROM tbl WHERE username=$1 AND domain=$2
UNION ALL
SELECT sendto, 2 AS priority FROM tbl WHERE username='*' AND domain=$2
ORDER BY priority
LIMIT 1;
Does that help at all?
--
Richard Huxton
Archonet Ltd
Communications on or through ioko's computer systems may be monitored or recorded to secure effective system operation and for other lawful purposes.
Unless otherwise agreed expressly in writing, this communication is to be treated as confidential and the information in it may not be used or disclosed except for the purpose for which it has been sent. If you have reason to believe that you are not the intended recipient of this communication, please contact the sender immediately. No employee is authorised to conclude any binding agreement on behalf of ioko with another party by e-mail without prior express written confirmation.
ioko365 Ltd. VAT reg 656 2443 31. Reg no 3048367. All rights reserved.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help writing a piece of SQL
@ 2006-02-03 12:59 Niklas Johansson <spot@tele2.se>
parent: Nigel Bishop <Nigel.Bishop@ioko.com>
1 sibling, 0 replies; 6+ messages in thread
From: Niklas Johansson @ 2006-02-03 12:59 UTC (permalink / raw)
To: Nigel Bishop <Nigel.Bishop@ioko.com>; +Cc: pgsql-sql
On 3 feb 2006, at 11.43, Nigel Bishop wrote:
> The query will have the username and domain passed in as variables.
> If the username and domain exist then return the sendto
> The bit I’m struggling with is if the username doesn’t exist then
> return the sendto where the domain exists
>
> e.g. username=fred (this doesn’t exist) and domain=rusty.com then
> returnrusty@rabbit.com, matilda.clematis@rabbit.com,
> stoat.griffin@rabbit.com
I think this could do the trick for you:
SELECT sendto FROM users t1
WHERE domain='rusty.com' AND (username='fred') = EXISTS(SELECT 1 FROM
users t2 WHERE username='fred' and domain=t1.domain);
Sincerely,
Niklas Johansson
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help writing a piece of SQL
@ 2006-02-03 13:06 Nigel Bishop <Nigel.Bishop@ioko.com>
0 siblings, 1 reply; 6+ messages in thread
From: Nigel Bishop @ 2006-02-03 13:06 UTC (permalink / raw)
To: spot@tele2.se; +Cc: pgsql-sql
Niklas,
Thank you very much, that did the business
Cheers
Nigel Bishop
-----Original Message-----
From: Niklas Johansson [mailto:spot@tele2.se]
Sent: 03 February 2006 13:00
To: Nigel Bishop
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Help writing a piece of SQL
On 3 feb 2006, at 11.43, Nigel Bishop wrote:
> The query will have the username and domain passed in as variables.
> If the username and domain exist then return the sendto
> The bit I'm struggling with is if the username doesn't exist then
> return the sendto where the domain exists
>
> e.g. username=fred (this doesn't exist) and domain=rusty.com then
> returnrusty@rabbit.com, matilda.clematis@rabbit.com,
> stoat.griffin@rabbit.com
I think this could do the trick for you:
SELECT sendto FROM users t1
WHERE domain='rusty.com' AND (username='fred') = EXISTS(SELECT 1 FROM
users t2 WHERE username='fred' and domain=t1.domain);
Sincerely,
Niklas Johansson
Communications on or through ioko's computer systems may be monitored or recorded to secure effective system operation and for other lawful purposes.
Unless otherwise agreed expressly in writing, this communication is to be treated as confidential and the information in it may not be used or disclosed except for the purpose for which it has been sent. If you have reason to believe that you are not the intended recipient of this communication, please contact the sender immediately. No employee is authorised to conclude any binding agreement on behalf of ioko with another party by e-mail without prior express written confirmation.
ioko365 Ltd. VAT reg 656 2443 31. Reg no 3048367. All rights reserved.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help writing a piece of SQL
@ 2006-02-03 13:20 Niklas Johansson <spot@tele2.se>
parent: Nigel Bishop <Nigel.Bishop@ioko.com>
0 siblings, 0 replies; 6+ messages in thread
From: Niklas Johansson @ 2006-02-03 13:20 UTC (permalink / raw)
To: Nigel Bishop <Nigel.Bishop@ioko.com>; +Cc: pgsql-sql
On 3 feb 2006, at 14.06, Nigel Bishop wrote:
> Thank you very much, that did the business
This should generate the same plan as the previous query, but be a
little bit more clean and easy to read:
SELECT sendto FROM users t1
WHERE domain='rusty.com' AND
username = COALESCE((SELECT username FROM users WHERE username='fred'
and domain=t1.domain), username);
Sincerely,
Niklas Johansson
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-02-03 13:20 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-03 10:43 Help writing a piece of SQL Nigel Bishop <Nigel.Bishop@ioko.com>
2006-02-03 11:01 ` Richard Huxton <dev@archonet.com>
2006-02-03 12:59 ` Niklas Johansson <spot@tele2.se>
2006-02-03 11:10 Re: Help writing a piece of SQL Nigel Bishop <Nigel.Bishop@ioko.com>
2006-02-03 13:06 Re: Help writing a piece of SQL Nigel Bishop <Nigel.Bishop@ioko.com>
2006-02-03 13:20 ` Niklas Johansson <spot@tele2.se>
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