Professional Web Applications Themes

Couple of small queries - MySQL

Hi, I have two (hopefully) simple queries about the following proposed table definition : mysql> describe charges; +---------------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+----------------------+------+-----+---------+-------+ | model_no | smallint(4) zerofill | NO | PRI | 0000 | | | pro_hour | decimal(10,2) | NO | | 0.00 | | | pro_half | decimal(10,2) | NO | | 0.00 | | | pro_day | decimal(10,2) | NO | | 0.00 | | +---------------+----------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> Query 1) There will always be an entry with a model_no of zero, which ...

  1. #1

    Default Couple of small queries

    Hi,

    I have two (hopefully) simple queries about the following proposed table
    definition :

    mysql> describe charges;
    +---------------+----------------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +---------------+----------------------+------+-----+---------+-------+
    | model_no | smallint(4) zerofill | NO | PRI | 0000 | |
    | pro_hour | decimal(10,2) | NO | | 0.00 | |
    | pro_half | decimal(10,2) | NO | | 0.00 | |
    | pro_day | decimal(10,2) | NO | | 0.00 | |
    +---------------+----------------------+------+-----+---------+-------+
    4 rows in set (0.00 sec)

    mysql>

    Query 1) There will always be an entry with a model_no of zero, which is
    used as the default entry; but there may not be an entry for all model
    numbers. I want a query that will give me the correct entry if there is
    one, or will give me the zero entry if there isn't. Would a query
    something along the lines of:

    SELECT * FROM charges WHERE model_no = $mod_no OR mod_no = zero ORDER BY
    model_no DESCENDING LIMIT 1;

    be a) Sensible, b) Work c) be the best way?

    Query 2) As you can probably guess, the three other fields are monetary
    values. At work I program using a codasyl database, rather than a sql
    database, wherein it is usual to define all monetary values as a whole
    number of pence, and only insert the pounds/pence separator (.) when we
    come to output the values. This makes all arithmetic on the values integer
    arithmetic which is obviously more efficient and faster. Given that here I
    am programming in perl, and as it happens doing almost no arithmetic at
    all on them, would it be sensible/reasonable to amend the definitions here
    to be whole integers for the same reason? I'm trying to persuade the
    database owner to make the changes as I think it's more logical, but maybe
    under mysql and perl it really doesn't matter. Ideas?

    Many thanks.

    Dave

    --
    Hexagon Systems Ltd Experts in VME solutions www.hexagon-systems.co.uk
    High Wycombe, Buckinghamshire. Anywhere in the United Kingdom

    Dave Guest

  2. #2

    Default Re: Couple of small queries

    On Thu, 01 Mar 2007 12:34:07 +0000 (GMT), Dave Stratford wrote: 

    Eh. You know how when you join two tables and if there's no entry in the
    second table, all its columns are null? Now go look over

    http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html

    right around letter C, and think about returning 0 instead of 1...
     

    It probably won't matter much for what you're doing. Integer monetary
    math is important for rounding reasons and that takes *lots* of
    transactions to mount up to anything appreciable. If you're mosly just
    summing up totals and counting things, the exposure's pretty low and the
    price of error down in the level of pence per month. It's probably not
    worth your time to complicate the code.

    --
    75. I will instruct my Legions of Terror to attack the hero en masse, instead
    of standing around waiting while members break off and attack one or two
    at a time.
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

  3. #3

    Default Re: Couple of small queries

    In article <ninehells.com>,
    Peter H. Coffin <com> wrote: [/ref]
     
     
     

    I've read this page and I'm not sure what you're telling me/what I'm
    supposed to be looking at. Will what I'm proposing above work? And is it
    the best way? If not, why not/what is?

    Thanks,

    Dave

    --
    Hexagon Systems Ltd Experts in VME solutions www.hexagon-systems.co.uk
    High Wycombe, Buckinghamshire. Anywhere in the United Kingdom

    Dave Guest

  4. #4

    Default Re: Couple of small queries

    On Fri, 02 Mar 2007 11:42:39 +0000 (GMT), Dave Stratford wrote: [/ref]



    >
    > I've read this page and I'm not sure what you're telling me/what I'm
    > supposed to be looking at. Will what I'm proposing above work? And is it
    > the best way? If not, why not/what is?[/ref]

    Null results will interfere with what you're asking for, and you will
    get null rresults if joining to this table from other tables. COALESCE()
    means that you can do a

    SELECT ... , COALESCE(charges.pro_hour,0)
    FROM ...

    which will give you exactly what you asked for: The correct entry if
    there is one, or a zero if there isn't.

    --
    100. Finally, to keep my subjects permanently locked in a mindless trance, I
    will provide each of them with free unlimited Internet access.
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

  5. #5

    Default Re: Couple of small queries

    In article <ninehells.com>,
    Peter H. Coffin <com> wrote: 
    > > 
    > > 
    > > 
    > >
    > > I've read this page and I'm not sure what you're telling me/what I'm
    > > supposed to be looking at. Will what I'm proposing above work? And is it
    > > the best way? If not, why not/what is?[/ref][/ref]
     
     
     

    I'm sorry, either I've not explained my problem properly, or I simply
    can't see how coalesce will help.

    Given the above table definition, if I have two entries as follows :

    mysql> select * from charges;
    +--------+----------+----------+---------+
    | mod_no | pro_hour | pro_half | pro_day |
    +--------+----------+----------+---------+
    | 0000 | 5.00 | 6.00 | 7.00 |
    | 0750 | 10.00 | 11.00 | 12.00 |
    +--------+----------+----------+---------+
    2 rows in set (0.00 sec)

    mysql>

    If I'm processing model 650, then I want to return values 5.00, 6.00 &
    7.00; but if I'm processing model 750, then I want to return 10.00, 11.00
    & 12.00.

    I've now managed to work out that the following query works:

    SELECT * FROM charges WHERE mod_no = $model OR mod_no = 0 ORDER BY mod_no
    DESC LIMIT 1;

    Where $mod_no is a perl variable.
    (I had to go and badger someone to set up the above table specifically so
    that I could test this!)

    I'm still curious to know whether this is the best way.

    Many thanks anyway.

    Dave

    --
    Hexagon Systems Ltd Experts in VME solutions www.hexagon-systems.co.uk
    High Wycombe, Buckinghamshire. Anywhere in the United Kingdom

    Dave Guest

  6. #6

    Default Re: Couple of small queries

    On Wed, 07 Mar 2007 08:55:48 +0000 (GMT), Dave Stratford wrote: 

    That's a pretty decent way to do it, though it's got a bit of that "too
    clever for its own good" thing about it. But, so long as it's doented
    well, it probably won't come back to bite you.

    --
    "Friendship is born at that moment when one person says to another, 'What!
    You too? I thought I was the only one!'"
    --C.S. Lewis
    Peter Guest

  7. #7

    Default Re: Couple of small queries

    On 7 Mar, 08:55, Dave Stratford <co.uk> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    > > Null results will interfere with what you're asking for, and you will
    > > get null rresults if joining to this table from other tables. COALESCE()
    > > means that you can do a
    > > SELECT ... , COALESCE(charges.pro_hour,0)
    > > FROM ...
    > > which will give you exactly what you asked for: The correct entry if
    > > there is one, or a zero if there isn't.[/ref]
    >
    > I'm sorry, either I've not explained my problem properly, or I simply
    > can't see how coalesce will help.
    >
    > Given the above table definition, if I have two entries as follows :
    >
    > mysql> select * from charges;
    > +--------+----------+----------+---------+
    > | mod_no | pro_hour | pro_half | pro_day |
    > +--------+----------+----------+---------+
    > | 0000 | 5.00 | 6.00 | 7.00 |
    > | 0750 | 10.00 | 11.00 | 12.00 |
    > +--------+----------+----------+---------+
    > 2 rows in set (0.00 sec)
    >
    > mysql>
    >
    > If I'm processing model 650, then I want to return values 5.00, 6.00 &
    > 7.00; but if I'm processing model 750, then I want to return 10.00, 11.00
    > & 12.00.
    >
    > I've now managed to work out that the following query works:
    >
    > SELECT * FROM charges WHERE mod_no = $model OR mod_no = 0 ORDER BY mod_no
    > DESC LIMIT 1;
    >
    > Where $mod_no is a perl variable.
    > (I had to go and badger someone to set up the above table specifically so
    > that I could test this!)
    >
    > I'm still curious to know whether this is the best way.
    >
    > Many thanks anyway.
    >
    > Dave
    >
    > --
    > Hexagon Systems Ltd Experts in VME solutions www.hexagon-systems.co.uk
    > High Wycombe, Buckinghamshire. Anywhere in the United Kingdom- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    Here are examples of the COALESCE method referred to earlier in the
    thread:

    This returns the values for 0750:

    SELECT
    COALESCE( `c2`.`pro_hour` , `c1`.`pro_hour` ) `pro_hour` ,
    COALESCE( `c2`.`pro_half` , `c1`.`pro_half` ) `pro_half` ,
    COALESCE( `c2`.`pro_day` , `c1`.`pro_day` ) `pro_day`
    FROM `charges` `c1`
    LEFT JOIN `charges` `c2` ON `c2`.`model_no` = 750
    WHERE `c1`.`model_no` = 0

    This returns the values for 0000:

    SELECT
    COALESCE( `c2`.`pro_hour` , `c1`.`pro_hour` ) `pro_hour` ,
    COALESCE( `c2`.`pro_half` , `c1`.`pro_half` ) `pro_half` ,
    COALESCE( `c2`.`pro_day` , `c1`.`pro_day` ) `pro_day`
    FROM `charges` `c1`
    LEFT JOIN `charges` `c2` ON `c2`.`model_no` = 650
    WHERE `c1`.`model_no` = 0

    Captain Guest

  8. #8

    Default Re: Couple of small queries

    In article <ninehells.com>,
    Peter H. Coffin <com> wrote: [/ref]
     

    LOL. Thanks. I think. :-)

    Dave

    --
    Hexagon Systems Ltd Experts in VME solutions www.hexagon-systems.co.uk
    High Wycombe, Buckinghamshire. Anywhere in the United Kingdom

    Dave Guest

  9. #9

    Default Re: Couple of small queries

    In article <googlegroups.com>,
    Captain Paralytic <com> wrote:
     
     
     
     
     

    Okay ... I think I see what you're getting at. Thank you very much for the
    practical demonstration.

    Dave.

    --
    Hexagon Systems Ltd Experts in VME solutions www.hexagon-systems.co.uk
    High Wycombe, Buckinghamshire. Anywhere in the United Kingdom

    Dave Guest

  10. #10

    Default Re: Couple of small queries

    On 8 Mar, 00:04, Dave Stratford <co.uk> wrote: 
    >
    > Okay ... I think I see what you're getting at. Thank you very much for the
    > practical demonstration.
    >
    > Dave.
    >
    > --
    > Hexagon Systems Ltd Experts in VME solutions www.hexagon-systems.co.uk
    > High Wycombe, Buckinghamshire. Anywhere in the United Kingdom- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    You're welcome

    Captain Guest

Similar Threads

  1. Lots of small queries vs one big query
    By listerofsmeg01@hotmail.com in forum MySQL
    Replies: 6
    Last Post: November 22nd, 09:36 PM
  2. Couple of contribute queries
    By bleasy in forum Macromedia Contribute General Discussion
    Replies: 4
    Last Post: June 16th, 12:00 AM
  3. Queries Of Queries Single Quote Problem
    By TimH2O in forum Macromedia ColdFusion
    Replies: 0
    Last Post: April 1st, 07:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139