January 30, 2009 at 7:30 am
Hi Folks.
I have 2 tables Parent and Child.
Parent Table
===========
RNO Name
1 abc
2 xyz
3 xxx
4 yyy
Child Table
============
TRN FRM TO
1 1 2
2 2 4
3 1 4
I want output like below
TRN FRM TO
1 abc xyz
2 xyz yyy
3 abc yyy
A quick repsonse is highly appreciated....
thanks
January 30, 2009 at 7:41 am
There are many ways to achieve this..., here is few ways to do it...
--METHOD 1
SELECTC.TRN, P1.Name AS FRM, P2.Name AS [TO]
FROMChild C
INNER JOIN Parent P1 ON C.FRM = P1.RNO
INNER JOIN Parent P2 ON C.TO = P2.RNO
--METHOD 2
SELECTC.TRN,
MAX( CASE WHEN C.FRM = P.RNO THEN P.Name ELSE '' END ) AS FRM,
MAX( CASE WHEN C.TO = P.RNO THEN P.Name ELSE '' END ) AS [TO]
FROMChild C
INNER JOIN Parent P ON C.FRM = P.RNO OR C.TO = P.RNO
GROUP BY C.TRN
--Ramesh
January 30, 2009 at 8:17 am
No ramesh....
I tried both above options... it does give the required output...
And morever I am using MS ACCESS.... Hence, I guess it doesn't take WHEN condition....
Help still needed.....
January 30, 2009 at 8:31 am
Rock (1/30/2009)
No ramesh....I tried both above options... it does give the required output...
And morever I am using MS ACCESS.... Hence, I guess it doesn't take WHEN condition....
Help still needed.....
Rock,
Two things. One, you posted your question in a SQL Server 2005 forum. Two, you didn't specify in your original post that you are using Access.
That's why you got a SQL Server 2005 answer.
There is a Access forum on SSC. This would be a better place for you to post your Access related questions. You will get more appropriate answers there.
January 30, 2009 at 8:35 am
Why did you post it in SQL 2005 forum, in first place? It just wastes posters time....
For the solution, I don't have much knowledge in ACCESS, but something like this should work....
SELECTC.TRN, P1.Name, P2.Name
FROMChild C, Parent P1, Parent P2
WHEREC.FRM = P1.RNO AND C.TO = P2.RNO
--Ramesh
January 30, 2009 at 9:14 am
Thanks Ramesh...
Its works fine... as per your suggestion.... And I will use right forum in future..:w00t:.
January 30, 2009 at 9:31 am
Rock (1/30/2009)
Thanks Ramesh...Its works fine... as per your suggestion.... And I will use right forum in future..:w00t:.
Thanks for the feedback, but make sure to post your queries in appropriate forums & also in the appropriate way, to know the appropriate way see this article Forum Etiquette[/url]
--Ramesh
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply