mySQL Query Returning duplicate results from INNER JOIN

Question

I need some help with a mySQL query I'm using in my php script. Seems I may be a bit confused on the way my INNER JOIN's are working.

SELECT * FROM CART_CARD
INNER JOIN INVOICE ON CART_CARD.cartID = INVOICE.cart_invoice_id
INNER JOIN PURCHASE_CARD ON PURCHASE_CARD.invoiceID = INVOICE.ID
WHERE CART_CARD.cardEmail = '[email protected]'
AND PURCHASE_CARD.ID BETWEEN 26118 AND 26620

So there are 3 tables - CART_CARD, INVOICE, and PURCHASE_CARD.

PURCHASE_CARD has invoiceID, which matches up to INVOICE.ID. INVOICE has a field card cart_invoice_id which matches up to CART_CARD.cartID.

I'm getting results, but it looks like the results are doubling and tripling the rows that are in CART_CARD. Does my query look right?

Results I'm getting back from CART_CARD. Please keep in mind that ID is the unique primary field. I've omitted the rest of the fields because it is sensitive information:

enter image description here

Always do SELECT DISTINCT unless you know exactly how and why duplicate rows are formed and that you want to have them. Normally, you don't. — Joker_vD
– Joker_vD, Commented Dec 2, 2013 at 1:35
You're not providing enough information (showing the resultset would be helpful) but a good guess is that PURCHASE_CARD returns more than one record which makes the records returned from INVOICE and CART_CARD become duplicated. — Nir Alfasi
– Nir Alfasi, Commented Dec 2, 2013 at 1:40
@tecshaun if in the results you posted ID is a field taken from PURCHASE_CARD then you can see that there are multiple records in PURCHASE_CARD which point to the same cart-id like I suggested earlier. — Nir Alfasi
– Nir Alfasi, Commented Dec 2, 2013 at 1:49
@Joker_vD and zeflex just suggested group by, but both of you tried to "fix the result" instead of getting down to the source of the issue. Further, both your suggestions wouldn't work. — Nir Alfasi
– Nir Alfasi, Commented Dec 2, 2013 at 1:51
@tecshaun since there are multiple records that fit your query you should ask yourself which one is relevant. If the answer is that any of them will do - you can create a sub-query that pulls this one record from PURCHASE_CARD by using a combination of join with the relevant table and LIMIT the resultset to 1. — Nir Alfasi
– Nir Alfasi, Commented Dec 2, 2013 at 1:53

Joshua Fabillar · Accepted Answer · 2013-12-02 03:05:04Z

2

SELECT DISTINCT FROM CART_CARD. showing only the unique records.

answered Dec 2, 2013 at 3:05

Joshua Fabillar

5962 gold badges10 silver badges24 bronze badges

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

Comments

Nir Alfasi · Accepted Answer · 2013-12-02 06:16:22Z

1

SELECT * FROM CART_CARD CC, INVOICE INV, 
 (
 SELECT * FROM PURCHASE_CARD 
 WHERE ID BETWEEN 26118 AND 26620
 AND invoiceID = INV.ID 
 LIMIT 1
 ) PRC
WHERE CC.cartID = INV.cart_invoice_id
AND CC.cardEmail = '[email protected]'
AND PRC.invoiceID = INV.ID

answered Dec 2, 2013 at 6:16

Nir Alfasi

53.6k12 gold badges94 silver badges138 bronze badges

2 Comments

Nicolas Over a year ago

Overly complicated while DISTINCT would be sufficient.

Nir Alfasi Over a year ago

@Nicolas assuming we can ignore all the other restrictions the OP posted in his query, you're right. This answer tries to cater both the DISTINCT requirement as well as the other ones which are reflected through the OP's query.

Collectives™ on Stack Overflow

mySQL Query Returning duplicate results from INNER JOIN

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related