CODEWITHSUNDEEP
mysqlsql
Tanya
Tanya

Reputation: 1

mysql select statement. why does it return 6 of the same record

When I make this sql statement I get 6 of the same record returned. So if I expect to get 2 records returned, I get six of each record back so that is 12 in total.

SELECT 
ce2.* 
FROM customerentry ce, customerentrytrace cet, customerentry ce2 
WHERE ce.accountid = 1 
AND ce.companyid = 1 
AND ce.accountid=cet.accountid 
AND ce.accountid=ce2.accountid 
AND ce.companyid=cet.companyid 
AND ce.companyid=ce2.companyid 
AND cet.documentno = '2012Faktura1' 
AND cet.documenttype = 1 
AND ce2.documentno = cet.offsetdocumentno 
AND ce2.documenttype = cet.offsetdocumenttype 
ORDER BY created;

I know that I can solve it by adding distinct, but I would like to know why I get 6 of the same record returned. Anyone who can help me?

Upvotes: 0

Views: 62

Answers (3)

Parixit
Parixit

Reputation: 3855

Problem might be because you have not properly joined tables. Please read about JOIN

SELECT ce2.* 
FROM customerentry ce INNER JOIN customerentrytrace cet ON ce.accountid=cet.accountid AND ce.companyid=cet.companyid, 

INNER JOIN customerentry ce2 ON ce.accountid=ce2.accountid AND ce.companyid=ce2.companyid AND ce2.documentno = cet.offsetdocumentno AND ce2.documenttype = cet.offsetdocumenttype 

WHERE ce.accountid = 1 
    AND ce.companyid = 1 
    AND cet.documentno = '2012Faktura1' 
    AND cet.documenttype = 1 

ORDER BY created;

Upvotes: 0

mhafellner
mhafellner

Reputation: 468

You are using an INNER JOIN, so for example there are two entries in table cet matching your where clause for combining table ce and cet, giving you 2 entries/entry of table ce.

Thinking this further you can see that if there are 3 entries in table ce2 matching the where clause for combining table cet and ce2 you get 3 entries/entry of table cet.

Which makes 6 entries per entry of table ce in total, giving you 12 entries in total even if you have only 2 entries in table ce.

So think again about what join could be the right for your desired solution.

Here a link for some more explanation: Short explanation of joins

Upvotes: 0

Omer
Omer

Reputation: 164

Since we have no idea about your table structure probably there are some columns that are related 1 to n items and you haven't handled them in the WHERE section of your query.

As an extra measure you can focus on your data needs and add a GROUP BY section before your ORDER section.

Upvotes: 1

Related Questions