From: Richard Huxton <dev@archonet.com>
To: Nigel Bishop <Nigel.Bishop@ioko.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Help writing a piece of SQL
Date: Fri, 03 Feb 2006 11:01:34 +0000
Message-ID: <43E3380E.7000004@archonet.com> (raw)
In-Reply-To: <72558D9A7573814BB4082A28843E03D402658B02@intyodc01.uk.ioko365.com>
References: <72558D9A7573814BB4082A28843E03D402658B02@intyodc01.uk.ioko365.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, Nigel.Bishop@ioko.com
Subject: Re: Help writing a piece of SQL
In-Reply-To: <43E3380E.7000004@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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