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
