X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 533219DC821 for ; Fri, 3 Feb 2006 07:01:35 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 82625-06 for ; Fri, 3 Feb 2006 07:01:37 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by postgresql.org (Postfix) with ESMTP id 627519DC816 for ; Fri, 3 Feb 2006 07:01:32 -0400 (AST) Received: from mainbox.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id D6E9F411032; Fri, 3 Feb 2006 11:01:34 +0000 (GMT) Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 9A87315EA4; Fri, 3 Feb 2006 11:01:34 +0000 (GMT) Message-ID: <43E3380E.7000004@archonet.com> Date: Fri, 03 Feb 2006 11:01:34 +0000 From: Richard Huxton User-Agent: Thunderbird 1.5 (X11/20051201) MIME-Version: 1.0 To: Nigel Bishop Cc: pgsql-sql@postgresql.org Subject: Re: Help writing a piece of SQL References: <72558D9A7573814BB4082A28843E03D402658B02@intyodc01.uk.ioko365.com> In-Reply-To: <72558D9A7573814BB4082A28843E03D402658B02@intyodc01.uk.ioko365.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.116 required=5 tests=[AWL=0.116] X-Spam-Score: 0.116 X-Spam-Level: X-Archive-Number: 200602/31 X-Sequence-Number: 24041 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