473,320 Members | 1,831 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Please help with duplicate entries.

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,

Expand|Select|Wrap|Line Numbers
  1. select job.Job_No,
  2. Working_Hrs*Paying_Rate,
  3. Quantity*Unit_Price
  4. from job left join labour
  5. on (job.Job_No=labour.Job_No)
  6. left join material
  7. on (job.Job_No=material.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.
Jan 4 '08 #1
2 1518
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*Paying_Rate,
Quantity*Unit_Price
from job left join labour
on (job.Job_No=labour.Job_No)
left join material
on (job.Job_No=material.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
Jan 4 '08 #2
mwasif
802 Expert 512MB
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT job.Job_No,
  2. Working_Hrs*Paying_Rate,
  3. Quantity*Unit_Price
  4. FROM job LEFT JOIN labour
  5. on (job.Job_No=labour.Job_No)
  6. LEFT JOIN material
  7. on (job.Job_No=material.Job_No)
  8. GROUP BY job.Job_No
Jan 5 '08 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Gary Lundquest | last post by:
It appears to me that MySQL version 4 returns an error messge when doing an Insert that results in duplicate entries. Version 3 did NOT return an error - it dropped the duplicate entries and ran...
4
by: sri2097 | last post by:
Hi all, I'm storing number of dictionary values into a file using the 'cPickle' module and then am retrieving it. The following is the code for it - # Code for storing the values in the file...
5
by: Chris Lasher | last post by:
Hello Pythonistas! I'm looking for a way to duplicate entries in a symmetrical matrix that's composed of genetic distances. For example, suppose I have a matrix like the following: A B ...
0
by: Cyberwolf | last post by:
OK, how to explain this. I have a table that will feed into another table using an append query. What I want to do is look at the table I am appending the record to to check for a duplicate...
1
by: dtefran3 | last post by:
The problem Implement a SortedList class which stores a list of int data in ascending order. Program requirements The public interface of your class is as follows: class SortedList{ public:...
1
by: michael.martinek | last post by:
Greetings! I've recently been trying to do something, which apparently looks like it may be a little odd.. I'm not finding anything in the manuals or anywhere on the web where something similiar...
2
by: mivey4 | last post by:
Okay I have 2 tables: Table A - holds a list of new hardware serial numbers and their corresponding model (no constraints or indexes) Table B - holds a distinct list of current serial numbers...
16
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...
7
by: php_mysql_beginer911 | last post by:
Hi .. i am trying to update a table where if field contents any duplictaed entries than one of the field should be updated with random number which is unique so i can make all entries unique i...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.