get data from 2 table based on a column value

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)

get data from 2 table based on a column value

Author

Topic

Yawer
Starting Member

1 Post

Posted - 2009-02-06 : 23:18:26

In table articles there are three columns.

1.ArticleID* 2.ISAdmin ** 3.SubmittedBy***

*it is primary key

***values here can come from 2 tables. adminID from admins table if article
was submited by admin or a memberId from members table if it was posted by
a member.

** true if article was submitted by admin and false if it was a member.

I want to select all ArticleID and base on column 2 (article was posted by admin or user) get relevnt user name from admins or members table.

I am not getting how to query for this? In sql query or procedure ?
plz help and guide me in this regard.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-02-06 : 23:33:56

u want something like this


SELECT	T.ArticleID
		, ISNULL(AD.AdminUserName, M.MemberUserName) AS 'SubmittedBy'
FROM		MainTable T
LEFT JOIN	AdminTable AD ON (T.IsAdmin = 1 AND T.SubmittedBy = AS.AdminID)
LEFT JOIN	Members M ON (T.IsAdmin = 0 AND T.SubmittedBy = M.MemberID)

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 09:08:59

quote:

Originally posted by PeterNeo

u want something like this


SELECT	T.ArticleID
		, ISNULL(AD.AdminUserName, M.MemberUserName) AS 'SubmittedBy'
FROM		MainTable T
LEFT JOIN	AdminTable AD ON (T.IsAdmin = 1 AND T.SubmittedBy = AS.AdminID)
LEFT JOIN	Members M ON (T.IsAdmin = 0 AND T.SubmittedBy = M.MemberID)

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

i would prefer to use COALESCE() here instead of ISNULL() unless i'm not sure about lengths of AdminUserName & MemberUserName

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 09:10:57

the reason being this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/10/04/isnull-or-coalesce.aspx

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-08 : 02:18:22

This method avoids ISNULL or COALESCE and should be somewhat more efficient than the left joins:


SELECT	T.ArticleID,
	CASE T.IsAdmin
	     WHEN 1 THEN (SELECT AdminUserName FROM AdminTable WHERE T.SubmittedBy = AdminID)
	     WHEN 0 THEN (SELECT MemberUserName FROM Members WHERE T.SubmittedBy = MemberID)
	END AS SubmittedBy
FROM	MainTable T

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources