Thread: 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