Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58

    Unanswered: Select where number is lower then

    Code:
    SELECT * FROM `database_is`.`slijtdelen_plasma`
    WHERE `aantal_huidig` < `aantal_min2` AND `leverancier`= '10'
    I'm using the above query but it doesn't give me result I hoped for.
    I have 2 tables:
    - aantal_huidig
    - aantal_min2

    I want to select where aantal_huidig is lower then aantal_min2. I have tried different setting but for some reason it's giving me always the wrong result.

    Any ideas?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im not sure I understand what you are tryingb to do...

    unless what you mean is you want to select from table 2 when table 2 is tless than table 1.... which if you think about it is not practical

    this question to me has a whiff of possibly suspect design

    perhaps you could supply details of the tbales, and explain what or how you select from which table
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you provide the SHOW CREATE TABLE for each table please?
    George
    Home | Blog

  4. #4
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58
    I have a system with spare items. My people in the workshop are changing the numbers of the spares when they are used. When the spares are below a certain level I need the order the spares.
    I don't want to order all the types but only the ones below a certain number of spares.

    Quote Originally Posted by healdem
    unless what you mean is you want to select from table 2 when table 2 is tless than table 1....
    That is basicly what I'm trying to do.

    Quote Originally Posted by healdem
    which if you think about it is not practical
    Do you think there is an better way?

  5. #5
    Join Date
    Feb 2008
    Location
    Bandung - Indonesia
    Posts
    15
    Quote Originally Posted by Muiter
    Code:
    SELECT * FROM `database_is`.`slijtdelen_plasma`
    WHERE `aantal_huidig` < `aantal_min2` AND `leverancier`= '10'
    I'm using the above query but it doesn't give me result I hoped for.
    I have 2 tables:
    - aantal_huidig
    - aantal_min2

    I want to select where aantal_huidig is lower then aantal_min2. I have tried different setting but for some reason it's giving me always the wrong result.

    Any ideas?
    You cant use logical operator between tables.
    But, you can compare some fields (table's attributes) values.

    IF aantal_huidig and aantal_min2 is the table name, you just cant compare it.

    Can you show us all of atributes reside on both table?

    Thanx.
    Forum Informatika - Indonesian Informatics Online Community - http://if.web.id

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok so what I'd expect for this is a table which identifies assembly/part
    a column within that which identifies what the Minimum Stock Quatityanothewr table whgich identifies stock movememnts (both actual and predicted, or just actual and another tbale indictaiung what has been ordered
    the I'd expect a query which would calcualte the stock on hand (ie quantity received - quantity issued/sold, takje into accoutn quantity that is orederedd by not yet delivered and rpoeort on thios at or near minimum Stock Level
    if you have items which have been ordered but not delivered, and they should have been delivered I'd be chasing thise
    if you have items at or near minimum stock level I'd be consoiderign ordering those
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58
    This is my table structure:
    CREATE TABLE IF NOT EXISTS `slijtdelen_plasma` (
    `bestelnummer` varchar(10) NOT NULL,
    `image` blob,
    `omschrijving` varchar(255) NOT NULL,
    `aantal_huidig` varchar(6) NOT NULL,
    `aantal_min` varchar(6) NOT NULL,
    `aantal_min2` varchar(6) NOT NULL,
    `bestelhoeveelheid` varchar(6) NOT NULL,
    `prijs` varchar(10) NOT NULL,
    `leverancier` int(6) NOT NULL,
    `updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    bestelnummer = ordernumber
    omschrijving = name
    aantal_huidig = in stock
    aantal_min = number when to start ordering where aantal_min2 is lower then aantal_huidig
    bestelhoeveelheid = order quantity
    prijs = price
    leverancier = order at

    Hope this helps to solve my problem.

  8. #8
    Join Date
    Feb 2008
    Location
    Bandung - Indonesia
    Posts
    15
    Still dont understand about your question! Maybe its help.

    Code:
    SELECT * FROM slijtdelen_plasma WHERE aantal_huidig < aantal_min2
    Btw, if its a number values, why dont you use a numeric type, like: smallint, int, currency, etc... instead varchar, its for a string!

    Maybe this is your problem.
    Last edited by galih; 01-10-09 at 22:30.
    Forum Informatika - Indonesian Informatics Online Community - http://if.web.id

  9. #9
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58
    Quote Originally Posted by galih
    Maybe this is your problem.
    It seems like it. I have changed it and the problem disappaered Thanks.

  10. #10
    Join Date
    Feb 2008
    Location
    Bandung - Indonesia
    Posts
    15
    You're welcome. Nice to see you
    Forum Informatika - Indonesian Informatics Online Community - http://if.web.id

Posting Permissions

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