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@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