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

    Join Date
    Aug 2017
    Posts
    15
    Rep Power
    0

    How to merge decimal data from one table to another


    Hi have two tables and wanting to collect the data from one table into another so that I can do a query on a greatest amount.

    I tried a few things but they don't work.

    Table 1:
    Code:
    `jb_job_data` (
      `id` int(11) NOT NULL,
      `job_value` decimal(10,2) NOT NULL
    )
    Table 2:
    Code:
    `jb_job_material` (
      `id` int(10) NOT NULL,
      `job_id` int(11) NOT NULL,
      `job_mat_qty` varchar(10) NOT NULL,
      `job_mat_unit` varchar(30) NOT NULL,
      `job_mat_desc` varchar(100) NOT NULL,
      `job_mat_code` varchar(30) NOT NULL,
      `job_mat_source` varchar(30) NOT NULL,
      `job_mat_status` varchar(30) NOT NULL,
      `job_mat_cost` decimal(10,0) DEFAULT NULL,
      `job_mat_retail` decimal(10,2) NOT NULL,
      `job_mat_trade` decimal(10,0) NOT NULL,
      `job_purchase_id` int(10) NOT NULL
    )
    Now the data has been entered into the table jb_job_material as jb_job_material.job_id = jb_job_data.id
    I want to merge jb_job_data.job_value into jb_job_material with the same name and data to the corresponding id
    hoping then that I can filter one table to see which has the greatest value e.g. at the moment I am testing:
    Code:
    SELECT 
    jb_job_data.id as jobid, jb_job_data.job_value as subtotal, jb_job_material.id as matid, jb_job_material.job_id as jobid, jb_job_material.job_mat_cost as subtotal, jb_job_material.job_mat_retail as subtotal
    
    FROM
    jb_job_data, jb_job_material
    
    WHERE
    jb_job_data.id = jb_job_material.job_id and GREATEST(jb_job_data.job_value,jb_job_material.job_mat_cost,jb_job_material.job_mat_retail) > '0:00'
    From that if it gives a value I can show this value else it will be 0:00

    I hope you can help I am either getting id is duplicating or it is -0
    Thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,746
    Rep Power
    4288
    Originally Posted by Striking
    I am either getting id is duplicating or it is -0
    please show some sample data to illustrate what you are getting
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,746
    Rep Power
    4288
    wait, never mind, i think the problem is NULLs in job_value

    try using COALESCE function --
    Code:
    SELECT jb_job_data.id                 as jobid
         , jb_job_data.job_value          as job_value
         , jb_job_material.id             as matid
         , jb_job_material.job_mat_cost   as job_mat_cost
         , jb_job_material.job_mat_retail as job_mat_retail
      FROM jb_job_data
    INNER
      JOIN jb_job_material
        ON jb_job_material.job_id = jb_job_data.id 
     WHERE GREATEST( COALESCE(jb_job_data.job_value,0)
                   , jb_job_material.job_mat_cost
                   , jb_job_material.job_mat_retail
                   ) > 0
    please note changes to your column alias names, also comparison to zero instead of character string '0:00'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2017
    Posts
    15
    Rep Power
    0
    Thank you that worked without errors.
    I tried COALESCE on all not just job_value and it didn't work or I was placing it wrong

    Is there a way to make it output only the greatest value that exists in either of those three fields: job_value, job_mat_cost,job_mat_retail so that I only get one result?

    I thought of adding them up and dividing by three but that won't work with some entries that exist. So even if the greatest value in all three is 0 then that is the value.

    Thank you so much for your help.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,746
    Rep Power
    4288
    Originally Posted by Striking
    Is there a way to make it output only the greatest value
    sure, just put the GREATEST expression into the SELECT clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2017
    Posts
    15
    Rep Power
    0
    Hi thanks I did a quick test on one entry but it gives me two values the first one is not part of this entry
    Code:
    SELECT GREATEST(jb_job_data.id, jb_job_data.job_value, jb_job_material.id, jb_job_material.job_mat_cost, jb_job_material.job_mat_retail)
             
      FROM jb_job_data
    INNER
      JOIN jb_job_material
        ON jb_job_material.job_id = jb_job_data.id
        Where jb_job_material.job_id = 1041
    the 1865 is not part of this wheras 1245 is correct

    Code:
    + Options
    GREATEST(jb_job_data.id, jb_job_data.job_value, jb_job_material.id, jb_job_material.job_mat_cost, jb_job_material.job_mat_retail) 	
    1865.00
    1245.00
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2017
    Posts
    15
    Rep Power
    0
    Actuall I see the 1865 is the jb_job_material.id when I use to check:

    Code:
    SELECT * FROM `jb_job_material` where `job_id` = 1041

    this is the output and the job_value in the jb_job_data is 1245 - have I done this correctly?

    Code:
    
    + Options
    Full texts 	id 	job_id 	job_mat_qty 	job_mat_unit 	job_mat_desc 	job_mat_code 	job_mat_source 	job_mat_status 	job_mat_cost 	job_mat_retail 	job_mat_trade 	job_purchase_id
    	Edit Edit 	Copy Copy 	Delete Delete 	1865 	1041 	1 		Charged price 				0 	1245.00 	0 	0
    	Edit Edit 	Copy Copy 	Delete Delete 	86 	1041 			White AL-03-30-3 x 2 (cut to 2 x 3000mm x 830mm) 				0 	0.00 	0 	0

IMN logo majestic logo threadwatch logo seochat tools logo