Search Postgresql Archives

Re: SQL query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Friday 11 February 2005 11:41, Janning Vygen wrote:
> Am Freitag, 11. Februar 2005 12:07 schrieb David Goodenough:
> > I have an address table, with all the normal fields and a customer name
> > field and an address type.  There is a constraint that means that the
> > combination of customer and type have to be unique.  Normally the
> > only record per customer will be of type 'default', but if for instance
> > the customer wants a different billing address I would add in a second
> > type='billing' address record.
> >
> > I then want to join this table to another table, say an invoice table,
> > and I want to use the billing address if present, otherwise the default
> > address.  I do not want to create either two addresses or to put both
> > addresses on the invoice.
> >
> > I could do this by doing a select * from addresses where customer = ?
> > and type = 'billing', looking to see if there is a result row and if not
> > repeating the query with type = 'default', but that seems inelegant to
> > me.
> >
> > I thought of using an inner select for the join, and using limit 1 to
> > get just the one, and forcing the order by to give me the billing
> > address by preference, but I am then dependant on the sort order
> > of the particular type values I am selecting from.
>
> don't think "vertical" (adresses in rows), think "horizontal" (adresses in
> columns), like this:
>
> SELECT
>   c.*,
>   COALESCE(a1.street, a2.street) AS street,
>   COALESCE(a1.zip, a2.zip) AS zip,
>   COALESCE(a1.town, a2.town) AS town
> FROM
>   customer AS c
>   LEFT JOIN adresses AS a1 USING (customer_id)
>   LEFT JOIN adresses AS a2 USING (customer_id)
> WHERE
>   a1.type = default
>   AND a2.type = 'billing'
>
> i just type the and did not tested it. the trick is to join adresses
> multiple times and get the right data with COALESCE function which returns
> the first value which is NOT NULL.
>
> If you still have difficulties, please send your schema.
>
> kind regards,
> janning

Lateral thinking always did appeal to me.  I will look into this further.

Thanks 

David

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux