SQL if statement...

lidds

Well-known member
Joined
Oct 19, 2004
Messages
122
Programming Experience
Beginner
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.

VB.NET:
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

VB.NET:
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
 
Hi Ian,

Thanks for the guidance, I have tried the following, but get an error:

Incorrect syntax near the keyword 'AS'.

VB.NET:
SELECT commentsTbl.ID, commentsTbl.commID, commentsTbl.commArea, commentsTbl.isPublishedComm, 
CASE
	WHEN commentsTbl.isPublishedComm = 'False' THEN
		userTbl.userFirst + ' ' + userTbl.userLast AS FullName
	ELSE
		'NOT FOUND' AS FullName
END                      
FROM commentsTbl LEFT OUTER JOIN userTbl ON commentsTbl.userCreatedID = userTbl.ID WHERE  commentsTbl.commArea = 'UK'

Any help would be apreshiated

Thanks

Simon
 
Hi,

You have it mixed up a bit. It should be:-

CASE WHEN commentsTbl.isPublishedComm = 'False' THEN userTbl.userFirst + ' ' + userTbl.userLast ELSE 'NOT FOUND' END AS FullName

Also be careful with commentsTbl.isPublishedComm = 'False'. If thats a boolean field then this will not work you will need to use commentsTbl.isPublishedComm = 0.

Cheers,

Ian
 
Thanks Ian,

One last question, is there a way that I can also handle it if userTbl.userFirst + ' ' + userTbl.userLast return NULL

VB.NET:
SELECT commentsTbl.ID, commentsTbl.commID, commentsTbl.commArea, commentsTbl.isPublishedComm, 
CASE
	WHEN commentsTbl.isPublishedComm = 'False' THEN
		userTbl.userFirst + ' ' + userTbl.userLast
                                [B]IF NULL THEN
                                  'NOT FOUND'
                                ENDIF[/B]	
             ELSE
		'NOT FOUND'
END                      
AS FullName
FROM commentsTbl LEFT OUTER JOIN userTbl ON commentsTbl.userCreatedID = userTbl.ID WHERE  commentsTbl.commArea = 'UK'

Thanks

Simon
 
Sorry to bother you again Ian,

I have tried this but get an error:

Incorrect syntax near 'FullName'.

VB.NET:
SELECT commentsTbl.ID, commentsTbl.commID, commentsTbl.commArea, commentsTbl.isPublishedComm, 
CASE
	WHEN commentsTbl.isPublishedComm = 'False' THEN
		userTbl.userFirst + ' ' + userTbl.userLast
    ELSE
		'NOT FOUND'
END                      
AS FullName
CASE
	WHEN FullName IS NULL THEN
		'NOT FOUND 1'
	ELSE		
END
FROM commentsTbl LEFT OUTER JOIN userTbl ON commentsTbl.userCreatedID = userTbl.ID WHERE  commentsTbl.commArea = 'UK'

Thanks for your time

Simon
 
Hi Simon,

You cannot do it like that. You are basically making a statement after your fieldname definition which is incorrect syntax for SQL. If you wanted to say this then you would say:-

ISNULL(CASE WHEN commentsTbl.isPublishedComm = 'False' THEN userTbl.userFirst + ' ' + userTbl.userLast ELSE 'NOT FOUND' END,'NOT FOUND') as FullName

But a better way to say this is:-

CASE WHEN commentsTbl.isPublishedComm = 'False' THEN ISNULL(userTbl.userFirst + ' ' + userTbl.userLast,'NOT FOUND') ELSE 'NOT FOUND' END) as FullName

Hope that helps.

Cheers,

Ian
 
Back
Top