Join help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Join help

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-08-13 : 01:39:15

Dear All,
I am using below query
select * from
(select
p.ERM 'ERMID',
p.projectname'ProjectName',
P.Owner 'Owner',
P.EfficiencyPotential 'Efficiancy Potential',
F.P90Fraction 'PBG P90 Fraction',
F.P90Efficiency 'PBG P90 Efficiancy',
P.P90DErating 'P90 Derating',
F.P50Fraction 'PBG P50 Fraction',
F.P50Efficiency 'PBG P50 Efficiancy',
P.P50Derating 'P50 Derating',
F.P90Unit1Date 'PBG Unit 1 P90 Qualification Date',
F.P90Unit2Date 'PBG Unit 2 P90 Qualification Date',
F.P50Unit1Date 'PBG Unit 1 P50 Qualification Date',
F.P50Unit2Date 'PBG Unit 2 P50 Qualification Date',

ch.newvalue
from
project p
join fanout f on p.erm = f.erm
JOIN PROJECTTYPE PT on PT.ProjectTypeID=P.ProjectTypeID
left join changehistory ch on p.erm=ch.erm
WHERE P.APPROVED = 0 and f.plantid = 'PBG'
)i

WHERE i.newvalue='Approved'

Here the problem is in the changehistory table there is no primary key to join with project table.
Changehistory table has a only relation of ERM.
When i run the above query it returns multiple rows for same ERM.
How can i restrict the number of rows return to only one row..??
Your help will be highly apprcetiated.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-13 : 02:12:56

don't post duplicate post
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=131107

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-13 : 02:17:19

so which row of changehistory do you want ?

the max value of [newvalue] ? try


JOIN PROJECTTYPE PT on PT.ProjectTypeID=P.ProjectTypeID
left join 
(
   select erm, newvalue = max(newvalue)
   from   changehistory 
   group by erm
) ch on p.erm=ch.erm

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources