#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2017
    Posts
    15
    Rep Power
    0

    How to fix two tables with wrong input


    Hi have taken over a project and the inputs for an invoice total have been put into different places. I need to fix these as well as adding tax,subtotal and total fields.

    This is the select I used to find this out and what it is doing and I have added images to illustrate what I mean:
    Code:
    SELECT 
    jb_job_data.id, jb_job_data.job_value, jb_job_material.id, jb_job_material.job_id, jb_job_material.job_mat_cost, jb_job_material.job_mat_retail
    
    FROM
    jb_job_data, jb_job_material
    
    WHERE
    jb_job_data.id = jb_job_material.job_id  
    ORDER BY `jb_job_data`.`id` ASC
    Ihttp://v2.visnetmedia.com.au/images/job1.png
    http://v2.visnetmedia.com.au/images/job2.png

    You can see that the total has been put into the jb_job_data table job_value field and is either in the jb_job_material table in job_mat_cost field or the job_mat_retail field or not at all.
    There is a sum of the jb_job_material fields that equal the jb_job_data.job_value but not always. There are a couple of entries into it that don't show up in the jb_job_material table at all but does in the jb_job_data.job_value

    So how to fix these so that they can show up as they should so I can have one field for subtotal - one field for tax - then add those to become a total field.

    I hope that makes sense.

    I am not sure if I have to alter the tables or just use some code to gather to show the correct information to move along
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    Originally Posted by Striking
    So how to fix these so that they can show up as they should
    not sure what "as they should" means
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2017
    Posts
    15
    Rep Power
    0
    Thanks for the quick reply, "as they should" means I need to gather one subtotal from all of the mess entered in the wrong tables and fields. I found another problem in this image attached.



    If the three fields are 0:00 then subtotal is 0:00

    If jb_job_data.job_value is 0:00 but the jb_job_material.job_mat_cost OR jb_job_material.job_mat_retail is more than 0:00 then that would be the subtotal.

    If jb_job_data.job_value is more than 0:00 but the jb_job_material.job_mat_cost OR jb_job_material.job_mat_retail is 0:00 then that would be the subtotal.


    Also in some entries the same amount has been put into jb_job_material.job_mat_cost AND jb_job_material.job_mat_retail so

    But in the last image the values are broken up in a few columns for jb_job_material.job_mat_cost AND jb_job_material.job_mat_retail so I can't divide them by 2 to get one amount.

    If you see the entries in the images it might explain better than me.

    Thanks
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2017
    Posts
    15
    Rep Power
    0
    Seeing it is too confusing for people to understand what I need I will start another thread.

IMN logo majestic logo threadwatch logo seochat tools logo