SQL that performs division

This may not be the best place for this but here goes...

I have a table that has some measures for recipes. Things like cup
pint gallon etc... I am trying to write something that can convert
between them easily. So far this is the direction i have taken:


I have a database that has, among other data:
Teaspoon 1
Tablespoon 3
Fluid OZ 6
Gill 24
Cup 48
Pint 96
Quart 192
Gallon 768
Firkin 6912
Hogshead 48384

That is a list of some of the values, it is column measure_name and
conversion_rate, the conversion rate for these measures are all
exressed in teaspoons.

The SQL query I wrote to do the conversion is here:
-- how many cups in a hogshead?
SELECT b1/a1 conversion FROM
(SELECT m.conversion_rate a1 FROM measure m
WHERE m.measure_name = 'cup') a, (SELECT m1.conversion_rate b1 FROM
measure m1
WHERE m1.measure_name = 'hogshead') b;

I'm using MySQL 5.0, im sure there are functions i can be using but
the join is what im worried about the most,

This to me is a very ugly SQL query and im thinking there's a better
way to do this, does anyone have any ideas on how i can improve this
query?

.

Relevant Pages

  • Re: SQL that performs division
    ... I have a database that has, ... The SQL query I wrote to do the conversion is here: ... -- how many cups in a hogshead? ... This to me is a very ugly SQL query and im thinking there's a better ...
    (comp.lang.java.databases)
  • Re: SQL that performs division
    ... the conversion rate for these measures are all ... The SQL query I wrote to do the conversion is here: ... WHERE m1.measure_name = 'hogshead') b; ... This to me is a very ugly SQL query and im thinking there's a better ...
    (comp.lang.java.databases)
  • Re: Cannot Get SQL Query to Work with "LIKE"
    ... would have expected ADO to do the conversion for you. ... an ADODB connection. ... If I use the following in the SQL query, ...
    (microsoft.public.excel.programming)