Re: SQL query

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