write a procedure for joins which avoids duplication of rows

Question

0.00/5 (No votes)

See more:

Previously I have posted a question based on joins,
but after writing the procedures I found there are duplication of columns going on.

Can you please guide me how do I use "group by" to avoid the duplications.

Thank you for the response, I got to learn new concepts.

Posted 14-Jul-11 20:27pm

priya naidu1

Updated 14-Jul-11 20:29pm

walterhevedeich

v2

Add a Solution

Comments

walterhevedeich 15-Jul-11 2:30am

Can you update your question and post the SQL script here.

priya naidu1 15-Jul-11 2:38am

select a.YearId,a.codeid,a.linkid,a.code, a.codename,
b.CodeId ,b.Code , b.CodeName ,c.Codeid,c.code,c.Codename,a.AcctType,d.FYId

from FinancialYear d,Accounts a left join Accounts b on(b.linkid=a.codeid)
left join Accounts c on(c.linkid=a.codeid)

where a.YearId=d.FYId and
b.CodeId=a.CodeId and
a.linkid=6 and a.flag<>0
and a.CodeId=9
and a.accttype='R'

This was the query which i have writen in my procedure, but here duplications are going on for all the columns. which means repeatation of the rows. Please help me in using the "group by "statement.

Timberbird 15-Jul-11 2:53am

Don't you have a column for primary key, say id, in you Accounts table? If you do, you could simply add conditions like AND b.id<>a.id and AND c.id<>a.idin your ON statements. BTW, I don't see any reason for the last join. Maybe it should be left join Accounts c on (c.linkid=b.codeid)?

priya naidu1 15-Jul-11 3:09am

Yeah, May be that was the reason. Anyways thanks for the correction.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manfred Rudolf Bihy · Accepted Answer · 2011-07-14T21:00:00

If you JOIN tables in SQL where table one has a one to many relationship to table two it is only natural what you are observing. If on the other hand you're talking about duplicate rows where whole rows are duplicated, this can be avoided by using the SELECT DISTINCT:

SQL

SELECT DISTINCT a.YearId, a.codeid, a.linkid, a.code, a.codename, a.AcctType,
                b.CodeId, b.Code, b.CodeName, 
                c.Codeid, c.code, c.Codename,
                d.FYId
FROM FinancialYear d, Accounts a 
    LEFT JOIN Accounts b ON(b.linkid=a.codeid)
    LEFT JOIN Accounts c ON(c.linkid=a.codeid)
WHERE
    a.YearId=d.FYId and
    b.CodeId=a.CodeId and
    a.linkid=6 and
    a.CodeId=9 and
    a.flag<>0 and
    a.accttype='R'

Cheers!