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

[jmcilhinney]

You just use one SELECT statement as normal but, for that column in the result set, you use a CASE expression:

ColumnA,
ColumnB =
    CASE commentsTbl.isPublishedComm
        WHEN 'True' THEN 'NOT FOUND'
        ELSE userTbl.userFirst + ' ' + userTbl.userLast
    END,
ColumnC

By the way, using a text column containing 'True' and 'False' is very dodgy. If that's what you're doing and you have control over the database then change it and do it properly with a bit column.