Обсуждение: SQL query
I realise this is not strictly a Postgreslql question, but if the best way to solve it involves using PG extensions, such as the PG procedural languages I am only going to do this on PG and so I am happy to use them. 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. Is there a better way? I am sure this kind of problem must have been solved before. Thanks in advance for any help you can give David
David Goodenough wrote: >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. > > Use NULL to indicate that the customer type is default. Then you can query with: select * from addresses where customer = ? and coalesce(type, 'billing') = 'billing' If type is NULL, the comparison will be 'billing' = 'billing' - always true. If there's a bunch of non-null type addresses,you'll get the 'billing' one. http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html#AEN12003 Matt
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
David Goodenough wrote: > I realise this is not strictly a Postgreslql question, but if the best way to > solve it involves using PG extensions, such as the PG procedural languages > I am only going to do this on PG and so I am happy to use them. > > 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. Not sure whether a schema change is possible for you, but you might want to have two tables - addresses (customer_id*, addr_id*, ...) addr_usage (customer_id*, addr_type*, addr_id) Add a custom trigger that ensures for every customer_id there is a valid row in addr_usage for each addr_type (sales, billing, shipping etc). That way you can have any mix of addresses you like, and it's explicit which address is for which purpose. -- Richard Huxton Archonet Ltd
On Fri, Feb 11, 2005 at 11:07:24 +0000, David Goodenough <david.goodenough@btconnect.com> wrote: > > 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. You can order by boolean expressions such as type = 'billing'. You can use that with LIMIT or DISTINCT ON to get just the address you want.
On Friday 11 February 2005 11:31, Matt K wrote: > David Goodenough wrote: > >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. > > Use NULL to indicate that the customer type is default. Then you can > query with: > > select * from addresses where customer = ? > and coalesce(type, 'billing') = 'billing' > > If type is NULL, the comparison will be 'billing' = 'billing' - always > true. If there's a bunch of non-null type addresses, you'll get the > 'billing' one. > > http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html#A >EN12003 > > Matt > Well coalesce is not something I had come across, learn something every day. But I can not use this as the type (with the customer) are the primary key and therefore not null. I could do something like:- coalesce( nullif( 'default', type), 'billing') but I think that might be over egging it a bit. I will hope this one reserve and remember coalesce for the future. Thanks, David
On Friday 11 February 2005 11:46, Richard Huxton wrote: > David Goodenough wrote: > > I realise this is not strictly a Postgreslql question, but if the best > > way to solve it involves using PG extensions, such as the PG procedural > > languages I am only going to do this on PG and so I am happy to use them. > > > > 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. > > Not sure whether a schema change is possible for you, but you might want > to have two tables - > addresses (customer_id*, addr_id*, ...) > addr_usage (customer_id*, addr_type*, addr_id) > Add a custom trigger that ensures for every customer_id there is a valid > row in addr_usage for each addr_type (sales, billing, shipping etc). > > That way you can have any mix of addresses you like, and it's explicit > which address is for which purpose. Interesting idea, I will consider this. Thanks David
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
On Friday 11 February 2005 13:39, Bruno Wolff III wrote: > On Fri, Feb 11, 2005 at 11:07:24 +0000, > > David Goodenough <david.goodenough@btconnect.com> wrote: > > 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. > > You can order by boolean expressions such as type = 'billing'. > You can use that with LIMIT or DISTINCT ON to get just the address you > want. Tried this, and got a rather un-intuative answer. If you have two relevant entries (one billing, the other default) and you:- order by type = 'billing' limit 1 you get the default one, if you:- order by type != 'billing' limit 1 you get the billing one. However:- order by type = 'billing' DESC limit 1 does get you the billing one. It makes sense in that false == 0 and true == 1 in many languages and 0 sorts before 1, but it still feels wrong. I had not realised I could use a comparison like this in order by. Thanks David