SQL that performs division

Thread view:

Chris - 25 Feb 2007 01:38 GMT

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?

Reply to this Message

Lew - 25 Feb 2007 15:48 GMT

> 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
[quoted text clipped - 30 lines]
> way to do this, does anyone have any ideas on how i can improve this
> query?

Consider using a front-end language like Java, C#, Basic, FORTRAN, Perl or
whatever to interact with the database. Let the database handle data. Let the
logic layer handle logic.

- Lew

Reply to this Message

Bruce Lewis - 28 Feb 2007 14:30 GMT

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

comp.databases would certainly be better. Sorry it's taking you so long
to get a good answer.

> 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
[quoted text clipped - 7 lines]
> measure m1
> WHERE m1.measure_name = 'hogshead') b;

SELECT hogshead.conversion_rate / cup.conversion_rate
FROM measure hogshead, measure cup
WHERE hogshead.measure_name = 'hogshead'
AND cup.measure_name = 'cup'

> 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?

If you're using mysql, chances are you're looking at bad examples. If
postgresql is available you'd be better off using it.

Signature

http://ourdoings.com/ Easily organize and disseminate news and
photos for your family or group.

Reply to this Message

SQL that performs division

Free Magazines