need of query - 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

General SQL Server Forums

New to SQL Server Programming

need of query

Author

Topic

vidhya
Posting Yak Master

108 Posts

Posted - 2008-10-14 : 09:53:06

Hi friends,

I am using 3 tables namely reference,link,user.
In reference table i am having fields refid,userid
In link table i am having fields linkid,refid,userid,date
In user table i am having fields userid,username.

I need to display the referencedby,linkcreatedname,date

referencedby-->display the name corresponding to the userid in reference table.

linkcreatedname-->display the name corresponding to the userid in link table.

date-->display the date from link table.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 09:57:56

Use INNER JOINs.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 10:01:49

[code]SELECT x.userName AS referencedBy,
u.userName AS linkCreatedName,
l.date
FROM Reference AS r
INNER JOIN Link AS l ON l.refID = r.refID
AND l.userID = r.userID
INNER JOIN [User] AS x ON x.userID = r.userID
INNER JOIN [User] AS u ON u.userID = r.refID[/code]

E 12°55'05.63"
N 56°04'39.26"

vidhya
Posting Yak Master

108 Posts

Posted - 2008-10-14 : 10:25:29

pepso,

referencedBy is displaying correctly ,but linkCreatedName is not displaying correct.

In reference table the values are
refid userid
4 2
5 2

In link table the values are
linkid refid userid date
1 4 5 3/2/2008
2 5 6 5/6/2008

In user table values are

userid username
2 shri
4 kumar
5 vidhya
6 raj

the result i m getting
shri kumar 3/2/2008
shri vidhya 5/6/2008

I need to get
shri vidhya 3/2/2008
shri raj 5/6/2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 11:03:31

modify like this

SELECT		x.userName AS referencedBy,
		u.userName AS linkCreatedName,
		l.date
FROM		Reference AS r
INNER JOIN	Link AS l ON l.refID = r.refID
INNER JOIN	[User] AS x ON x.userID = r.userID
INNER JOIN	[User] AS u ON u.userID = l.userID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources