Re: 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(at)rabbit(dot)com
> stoat.griffin | trusting.co.uk | stoat(dot)griffin(at)rabbit(dot)com
> stoat.griffin | trusting.com | stoat(dot)griffin(at)rabbit(dot)com
> stoat.griffin | rusty.co.uk | stoat(dot)griffin(at)rabbit(dot)com
> stoat.griffin | rusty.com | stoat(dot)griffin(at)rabbit(dot)com

> * | trusting.com | rusty(at)rabbit(dot)com
> * | trusting.co.uk | rusty(at)rabbit(dot)com
> * | rusty.co.uk | rusty(at)rabbit(dot)com
> * | rusty.com | rusty(at)rabbit(dot)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(at)rabbit(dot)com, matilda(dot)clematis(at)rabbit(dot)com,
> stoat(dot)griffin(at)rabbit(dot)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