SQL if statement...

[lidds]

I have an SQL statement that gathers the users fullname from a userTbl table using the userID value in the commentsTbl table. What I want to be able to do is if the value of commentsTbl.isPublishedComm equal to 'True' then add a value into the FullName field e.g. 'NOT FOUND' else if commentsTbl.isPublishedComm equal to 'False' then use LEFT OUTER JOIN code.

SELECT commentsTbl.ID, commentsTbl.commID, commentsTbl.commArea,commentsTbl.isPublishedComm, userTbl.userFirst + ' ' + userTbl.userLast AS FullName
FROM commentsTbl LEFT OUTER JOIN userTbl ON commentsTbl.userCreatedID = userTbl.ID WHERE commentsTbl.commArea = 'UK'

So would be something like

If commentsTbl.isPublishedComm = 'True' then
SELECT commentsTbl.ID, commentsTbl.commID, commentsTbl.commArea,commentsTbl.isPublishedComm, 'NOT FOUND' AS FullName
FROM commentsTbl LEFT OUTER JOIN userTbl ON commentsTbl.userCreatedID = userTbl.ID WHERE commentsTbl.commArea = 'UK'
elseif commentsTbl.isPublishedComm = 'False' then
SELECT commentsTbl.ID, commentsTbl.commID, commentsTbl.commArea,commentsTbl.isPublishedComm, userTbl.userFirst + ' ' + userTbl.userLast AS FullName
FROM commentsTbl LEFT OUTER JOIN userTbl ON commentsTbl.userCreatedID = userTbl.ID WHERE commentsTbl.commArea = 'UK'
endif

Thanks

Simon

 

[MarkSweetland]

You can do it all within the SQL statement:

SELECT 
	commentsTbl.ID, commentsTbl.commID, commentsTbl.commArea,commentsTbl.isPublishedComm, 
	case when commentsTbl.isPublishedComm = 'True' then
		'Not found' 
	else 
		userTbl.userFirst + ' ' + userTbl.userLast 
	end as FullName
FROM 	commentsTbl 
	LEFT OUTER JOIN userTbl ON commentsTbl.userCreatedID = userTbl.ID 
WHERE	commentsTbl.commArea = 'UK'

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach