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
