SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 repeated records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

divyaat
Starting Member

India
1 Posts

Posted - 03/05/2014 :  12:07:52  Show Profile  Reply with Quote
Hi ,

Could you please help me in finding the solution for the below query?

It displays repeated records.

Kindly help me on this since am a beginner.

select distinct ku.username,rro.role_name,rp.resource_type_code,kr.region_name,kc.currency_name,
fcr.cost_rule_id RULE,fcr.rate current_rate,pp.project_name,kou.org_unit_name ORG_UNIT
from
knta_users ku,RSC_RESOURCES rr,RSC_RESOURCE_ROLES rrr,RSC_ROLES rro,
RSC_POSITIONS rp,KNTA_REGIONS kr,KCST_CURRENCIES kc,FM_COST_RATES fcr,FM_COST_RULES fcru,
PM_PROJECTS pp,KRSC_ORG_UNITS kou

where

ku.user_id = rr.user_id
and rr.resource_id = rrr.resource_id
and rrr.role_id = rro.role_id
and rro.role_id = rp.role_id
and ku.region_id = kr.region_id
and kr.currency_id = kc.currency_id
and kc.currency_code = fcr.currency_code
and fcr.cost_rule_id = fcru.cost_rule_id
and kr.region_id = pp.region_id
and ku.manager_user_id = kou.manager_id

Kind Regards

tkizer
Almighty SQL Goddess

USA
36851 Posts

Posted - 03/05/2014 :  12:11:46  Show Profile  Visit tkizer's Homepage  Reply with Quote
It displays repeated records because you have a one to many relationship between two or more of the tables. You'll need to determine which rows you'll want to keep. ROW_NUMBER() function can be helpful for this.

And you need to start using proper JOIN syntax. Join conditions should not be in the WHERE clause. You should have only one table in the FROM. Use joins for the other tables.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

maunishq
Yak Posting Veteran

Canada
71 Posts

Posted - 03/05/2014 :  13:51:44  Show Profile  Reply with Quote
Which database are you using?

=======================
Not an Expert, Just a learner.
!_(M)_!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000