Returning too many fields??

[swanside]

Hello.

Can somebody please help me? I have made a site for a helpdesk. It has tables which are, job, labour, customer and material. I have wrote an SQL statement which is looking at the job table and gathering the labour used and the material used. It is adding anadditional cost onto the material, as well as a VAT content of 17.5% on the material and the labour. The calculation works fine if there is only 1 entry for the labour and 1 entry for the material. But, if I have 2 entries for the labour and 1 entry for the material, it looks at the labour and doubles the entries for the materials.

 

Here is my SQL script.

 

select job.Job_No,
job.Order_Date,
job.File_No,
job.Order_Site_Address,
job.Job_Description,
job.CustomerRef,
job.Invoice_Tax_Date,
customer.Customer_Name,
customer.Billing_Address,
labour.Working_Hrs * Paying_Rate,
labour.Working_Hrs * Paying_Rate * 0.175,
(material.Quantity * Unit_Price * Additional_Cost / 100 + Quantity * Unit_Price)*0.175,
material.Quantity * Unit_Price + (Quantity * Unit_Price * Additional_Cost / 100),
(labour.Working_Hrs * Paying_Rate + material.Quantity * Unit_Price + Quantity * Unit_Price * Additional_Cost / 100) * 0.175,
labour.Working_Hrs * Paying_Rate + material.Quantity * Unit_Price + (Quantity * Unit_Price * Additional_Cost / 100),
(labour.Working_Hrs * Paying_Rate + material.Quantity * Unit_Price + Quantity * Unit_Price * Additional_Cost / 100) * 0.175 + labour.Working_Hrs * Paying_Rate + material.Quantity * Unit_Price + (Quantity * Unit_Price * Additional_Cost / 100) 
from job left join labour
on (job.Job_No=labour.Job_No)
left join material
on (job.Job_No=material.Job_No)
left join customer
on (job.Customer_Name=customer.Customer_Name)

 

I have made it using PHPRunner. I have upped it to a demo account at http://demo.asprunner.net/Account/Login.aspx

 

The email is [email protected] and the password is 1234

 

Thanks for any help

 

Paul.

[fenway]

Why would there be two entries? You'll need to limit this either in the join

[swanside]

If we send 1 engineer to a job, they might be there for an hour and use an item, but he might need a hand to fit it, so another engineer would assist resulting in engineer 'A' booking 2 hours, engineer 'B' booking 1 hour, with engineer 'A' booking materials. They might have one item or fifty items, they all get put into the database under a Job_No which is set to auto increment on the next new job.

 

I have nearly got my project finished now, just needs the pages to be setup according to our desires, but, I am getting this problem.

[Barand]

That's the nature of joins

[pre]

labour                      material                          joined result

 

Job    |  Cost              Job    |  MatCost                Job    |  Cost  |  MatCost

-------+---------            -------+---------                  -------+---------+---------

A      |  50                A      |  20                      A      |  50    |  20     

                            A      |  30                      A      |  50    |  30

                           

[/pre]

                           

You can reduce the labour and material costs to a single row each using subqueries to pre-calculate the job totals prior to joining with the other tables 

[swanside]

Cheers, Looks like its back to the sql manual.

 

Thanks for your help

Swanny

[Barand]

example

SELECT job.Job_no, job.Order_Date, m.totcost, l.totcost
FROM job
LEFT JOIN (SELECT job_no, SUM(cost) as totcost FROM labour GROUP BY job_no) as l ON job.job_no=l.job_no
LEFT JOIN (SELECT job_no, SUM(matcost) as totcost FROM material GROUP BY job_no) as m ON job.job_no=m.job_no

[swanside]

Cheers for the example, Will start with that.

 

Thanks again

Paul.

[swanside]

Thanks for the example, but I could not figure it out, I kept trying different fields, but I was gettin errors.

 

Is there a basic guide to grouping results anywhere that I can read up on please?

 

Thanks

Paul.

[Barand]

try http://www.w3schools.com/sql/sql_groupby.asp

[swanside]

Thank you. Will have a read up.