Re: SQL query

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