mysql query returning the same row multiple times

Question

I'm having a difficult time figuring out why my mysql query is returning the same row five times. Here is the query below. I read on here something about joins and cartisian products. I'm very new to this, and editing this query on my site that someone else initially created. Trying to understand the joins that are used here, but it's confusing me. Any help is greatly appreciated. The goal is to get each row to return only once, not five times. Thanks!

SELECT off . * , resp.tracking_date, resp.paid_date, resp.total_value, resp.total_conversion
FROM tbl_offers AS off, tbl_tracking AS tr, tbl_trackvalue AS resp, tbloffers_cat AS offcat
WHERE off.id = tr.offer_id
AND off.offer_type = 'cash_offer'
AND off.id = offcat.offer_id
AND tr.id = resp.tracking_id
AND tr.user_id = '1454'
AND resp.total_value !=0
AND resp.payment_status = 'paid'
LIMIT 0 , 30

Wow, thanks for all the help everyone. Looks like a lot of the solutions mentioned here would work. I did get the result I want by adding a group by tracking_id to the query. Not sure if that's actually the best way to do it or not. I'm just learning to program while editing an old site, which I initially had someone else build. Lot's of fun. The whole join thing is one of the more complicated things I've dealt with so far, but I'm sure it'll get easier.

I'll go through these answers to pick the best one, though it looks like multiple could work, so I'm not sure what the standard method of picking is on this site. Thanks again!

Its working properly.Try using DISTINCT keyword.

uhs
– uhs

2014-01-22 05:16:02 +00:00
Commented Jan 22, 2014 at 5:16 — uhs
– uhs, Commented Jan 22, 2014 at 5:16

Hart CO · Accepted Answer · 2014-01-22 05:23:53Z

2

While your query should be working as intended, it's using deprecated syntax, I suggest replacing the deprecated implicit joins with standard explicit joins:

SELECT off. * , resp.tracking_date, resp.paid_date, resp.total_value, resp.total_conversion
FROM tbl_offers AS off
JOIN tbl_tracking AS tr
 ON off.id = tr.offer_id
JOIN tbl_trackvalue AS resp
 ON tr.id = resp.tracking_id
JOIN tbloffers_cat AS offcat
 ON off.id = offcat.offer_id
WHERE off.offer_type = 'cash_offer'
  AND tr.user_id = '1454'
  AND resp.total_value !=0
  AND resp.payment_status = 'paid'
LIMIT 0 , 30

As to the multiple records, if the JOIN criteria doesn't identify a one to one relationship then you'll get multiple rows where you expected 1. Try selecting fields from all tables in your query to see which table has multiple values being returned. Perhaps there are multiple tracking records with the same offer_id, but you wouldn't see that in your query because you aren't selecting anything from the tracking table.

answered Jan 22, 2014 at 5:23

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

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

2 Comments

programmingnewb Over a year ago

When you use this syntax what type of join is being performed?

Hart CO Over a year ago

@user2616350 JOIN = INNER JOIN, LEFT JOIN = LEFT OUTER JOIN, RIGHT JOIN = RIGHT OUTER JOIN

Barry Colebank Jr · Accepted Answer · 2014-01-22 05:26:08Z

0

It doesn't look like tbloffers_cat is used in the query, and may contain rows that aren't visible in your result set. To verify this, add tbloffers_cat.* to your query and see if the values in those fields differ from row to row.

If that's the case, try this:

SELECT off.*
    , resp.tracking_date
    , resp.paid_date
    , resp.total_value
    , resp.total_conversion
FROM tbl_offers AS off
    INNER JOIN tbl_tracking AS tr ON tr.offer_id = off.id
    INNER JOIN tbl_trackvalue AS resp ON resp.tracking_id = tr.id
WHERE off.offer_type = 'cash_offer'
AND tr.user_id = '1454'
AND resp.total_value !=0
AND resp.payment_status = 'paid'
LIMIT 0 , 30

In my opinion, The syntax of inner joins is usually clearer when looking at the query.

answered Jan 22, 2014 at 5:26

Barry Colebank Jr

1,9492 gold badges16 silver badges17 bronze badges

9 Comments

programmingnewb Over a year ago

While everyone was helpful, yours got me the results I need without adding my group by workaround. All the other answers still returned multiple rows. Still don't fully understand why it works, but now I can dig into your solution to understand the differences. Thanks.

programmingnewb Over a year ago

Looks like it has to do with inner join vs. left join. Inner join is getting me only the results I need, whereas left join was returning multiple rows for the same data.

Barry Colebank Jr Over a year ago

Without seeing the entire schema of the tables, the best I can tell you is that there must have been multiple rows in the tbloffers_cat table that match more than one record when you're looking just at tbloffers_cat.offer_id. I have a hunch that those tables actually match on different criteria.

Barry Colebank Jr Over a year ago

Any chance the offer_id is null in the tbl_offers table?

programmingnewb Over a year ago

You were correct that tbloffers_cat has an id column that differs between each of the returned rows. This wasn't showing before I added it to the select. Why would it take that into account though considering the original query doesn't mention the offcat.id at all? It mentions offcat.offer_id = off.id (those two always did match in each row). So why would it matter when none of the mentioned columns in offcat were different? Thanks again for the help.

|

Vignesh Kumar A · Accepted Answer · 2014-01-22 05:23:43Z

0

You have a cartesian product in your query.

You should JOIN tbl_offers and all other table like this

FROM tbl_offers a JOIN tbl_tracking u ON a.id = u.offer_id

So try like this

SELECT off. * , resp.tracking_date, resp.paid_date, resp.total_value, resp.total_conversion
FROM tbl_offers AS off
JOIN tbl_tracking AS tr
 ON off.id = tr.offer_id
JOIN tbl_trackvalue AS resp
 ON tr.id = resp.tracking_id
JOIN tbloffers_cat AS offcat
 ON off.id = offcat.offer_id
WHERE off.offer_type = 'cash_offer'
  AND tr.user_id = '1454'
  AND resp.total_value !=0
  AND resp.payment_status = 'paid'
LIMIT 0 , 30

Read more about joining tables here: http://dev.mysql.com/doc/refman/5.5/en/join.html

answered Jan 22, 2014 at 5:23

Vignesh Kumar A

28.6k14 gold badges67 silver badges119 bronze badges

2 Comments

Hart CO Over a year ago

While I agree that explicit joins should be used, the OP's query does define the joins in the where criteria.

programmingnewb Over a year ago

I'm not sure if this is the best solution, but I am now getting the result I want by adding: group by tracking_id to the query. I'm still learning while updating my site, so I'm sure I'll learn more about why that worked as I continue.

Ashish Jagtap · Accepted Answer · 2014-01-22 05:25:31Z

0

SQL JOINS

Codeproject has this image which explains the simple basics of SQL joins, taken from:

Visual Representation of SQL Joins

enter image description here

update your query as follows here

SELECT off.*,resp.tracking_date, resp.paid_date, resp.total_value, resp.total_conversion
FROM tbl_offers AS off
LEFT JOIN  tbl_tracking AS tr ON tr.offer_id = off.id
LEFT JOIN  tbl_trackvalue AS resp ON  resp.tracking_id = tr.id 
LEFT JOIN  tbloffers_cat AS offcat ON offcat.offer_id = off.id
WHERE  off.offer_type = 'cash_offer'
AND tr.user_id = '1454'
AND resp.total_value != 0
AND resp.payment_status = 'paid'
LIMIT 0 , 30

hope this will solve your problem

answered Jan 22, 2014 at 5:25

Ashish Jagtap

2,8193 gold badges33 silver badges46 bronze badges

Collectives™ on Stack Overflow

mysql query returning the same row multiple times

4 Answers 4

2 Comments

9 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

9 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related