MySQL Join is showing multiple rows with same value

Question

I am trying to learn MySQL select statements a little better and I have somehow ended up selecting multiple rows with the same values. If I remove the last JOIN the query comes out correctly, but I still need the tasks.Rate_Schedule_ID joined with hourlyrates.Rate_Schedule_ID, as shown in the last JOIN statement.

EDIT: It is duplicating once for each row. Here is a picture of the results I am getting.

enter image description here

 SELECT 
 project_timecard_tasks.Task_ID, 
 project_timecard_tasks.DateTime,
 project_timecard_tasks.Total_Hours,
 project_timecard_tasks.User_ID,
 project_timecard_tasks.Project_ID,
 users.User_ID,users.FirstName,
 users.LastName,
 tasks.id,
 tasks.taskName, 
 tasks.billingOption,
 tasks.fixedRate,
 tasks.Rate_Schedule_ID,
 hourlyrates.Rate_Schedule_ID,
 hourlyrates.hourlyRate

FROM 
 project_timecard_tasks 

JOIN users ON project_timecard_tasks.User_ID = users.User_ID 
JOIN tasks ON project_timecard_tasks.Task_ID = tasks.id 
JOIN hourlyrates ON tasks.Rate_Schedule_ID = hourlyrates.Rate_Schedule_ID

WHERE 
 project_timecard_tasks.Project_ID = '$jobNumber'

How many rows in hourlyrates match to each record from tasks? — zerkms
– zerkms, Commented Jun 12, 2012 at 23:49
if only one row matches - then it is impossible that the last causes that issue. I'm sure that there are more than one row that fits tasks.Rate_Schedule_ID = hourlyrates.Rate_Schedule_ID condition. So I highly advice you to double check that — zerkms
– zerkms, Commented Jun 12, 2012 at 23:52

juacala · Accepted Answer · 2012-06-13 00:02:22Z

2

The issue is that you have multiple rows in your result (from the tasks table) with the same Rate_Schedule_ID. That means that for each of those, you get a match in your hourlyrates table, which gives you two matches for each row.

You can change your SELECT to SELECT DISTINCT, which will fix the problem. But this might be an indication that there is a deeper problem in your data.

The way to check if this is really the issue is to look at the result set without the last join. If there are duplicate Rate_Schedule_IDs, then that's why it's duplicating rows.

answered Jun 13, 2012 at 0:02

juacala

2,2351 gold badge23 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

zerkms Over a year ago

SELECT DISTINCT won't solve the issue. And in most cases DISTINCT usage is an indicator that you're doing something wrong

juacala Over a year ago

It will fix the output so that it doesn't duplicate results, but it won't solve the underlying issue of why it's happening. It also won't help performance any.

Collectives™ on Stack Overflow

MySQL Join is showing multiple rows with same value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related