Please help with duplicate entries.

[swanside]

Hello all, Found this forum Google as I am trying to find out why my database is returning multiple results when I use the JOIN to join two tables together.

Here is the code I am using,

[CODE=mysql]select job.Job_No,
Working_Hrs*Pay ing_Rate,
Quantity*Unit_P rice
from job left join labour
on (job.Job_No=lab our.Job_No)
left join material
on (job.Job_No=mat erial.Job_No)[/CODE]

I have uploaded it to a demo account,I am using ASPRunner, but I get the same problems with PHPRunner, so I know its solution is somewhere in my SQL code.

Here is the URL for the demo Demo Site

If it asks for a username, its the same as my email
[Email removed]
The password is
1234

You can see that there are two details for the entries in Labour, and one detail for the entry in materials, but when you look on the invoice tab, it has duobled the materials to show two entries, this will increase if the labour entries are added to as well.

This is the final part I need to finish my project, but I have been stuck on this for a good week now, trying inner joins, leftjoins right joins, I tried adding Group by job.Job_No, but this only came back with the first line in the labour entry, so thats no good either.

Please, there must be somebody who can shred some light on this for me.

Thanks in advance
Paul.

[cyberking]

Hello all, Found this forum Google as I am trying to find out why my database is returning multiple results when I use the JOIN to join two tables together.

Here is the code I am using,

select job.Job_No,
Working_Hrs*Pay ing_Rate,
Quantity*Unit_P rice
from job left join labour
on (job.Job_No=lab our.Job_No)
left join material
on (job.Job_No=mat erial.Job_No)

I have uploaded it to a demo account,I am using ASPRunner, but I get the same problems with PHPRunner, so I know its solution is somewhere in my SQL code.

Here is the URL for the demo Demo Site

If it asks for a username, its the same as my email.,
[Email Removed]
The password is
1234

You can see that there are two details for the entries in Labour, and one detail for the entry in materials, but when you look on the invoice tab, it has duobled the materials to show two entries, this will increase if the labour entries are added to as well.

This is the final part I need to finish my project, but I have been stuck on this for a good week now, trying inner joins, leftjoins right joins, I tried adding Group by job.Job_No, but this only came back with the first line in the labour entry, so thats no good either.

Please, there must be somebody who can shred some light on this for me.

Thanks in advance
Paul.

Hi!
Sorry mate! The link aint seem to be functional... U sure its there??
Would be good if yoou could post the table / data here rather than as a link!

Regards,
CyberKing

[mwasif]

Hi swanside,
Do not post your email address. Kindly provide the required data as cyberking suggested.

May be you need GROUP BY on job.Job_No e.g.
[CODE=mysql]SELECT job.Job_No,
Working_Hrs*Pay ing_Rate,
Quantity*Unit_P rice
FROM job LEFT JOIN labour
on (job.Job_No=lab our.Job_No)
LEFT JOIN material
on (job.Job_No=mat erial.Job_No)
GROUP BY job.Job_No[/CODE]