1

I want if Status column = 1

Check If there are rows in another table return 'Check' and If no rows return 'In DB'

SELECT ID, UserName,
CASE [Status]
    WHEN 1 THEN
        if ((Select Count(*) From Logs_TB Where Logs_TB.UserName = Users_TB.UserName) > 0)
            'Check'
        Else
            'In DB'
    WHEN 2 THEN 'Revision'
    WHEN 3 THEN 'Sent'
END AS StatusName
FROM Users_TB CROSS JOIN Logs_TB

Edit 1:

I have Two Tables

in First Table. I want to get the following result for Column [Status]

if FirstTable.ColumnStatus = 1
   if SecondTable.ColumnA = FirstTable.ColumnB Has Rows
        'Check'
   else
        'In DB'
else if FirstTable.ColumnStatus = 2
   'Revision'
else if FirstTable.ColumnStatus = 3
   'Sent'

Edit 2

enter image description here

This is an example

I want Select All Rows From Employment Table

and I want to parse column Status to Column As "StatusName"

if Status = 1 It has two values

First value Check if QualificationID and SpecializationID Has Rows in Table 'Vacancies' return 'Check'

and if no rows in Table 'Vacancies' return 'In DB'

if Status = 2 'Revision'

if Status = 3 'Sent'

Ricky Suhendar
  • 125
  • 1
  • 10
  • Add another nested `CASE` or `IIF` if you are on 2012+. Also best checking `EXISTS` rather than `COUNT > 0` – Martin Smith Oct 24 '15 at 20:43
  • I wanted to post this as answer but it will be comment. You need more advanced case when and better use EXISTS instead of COUNT > 0 `SELECT ID, UserName, CASE WHEN [Status]= 1 AND EXISTS (SELECT 1 FROM Logs_TB Where Logs_TB.UserName = Users_TB.UserName) THEN 'Check' WHEN [Status] = 1 THEN 'In DB' WHEN [Status] = 2 THEN 'Revision' WHEN [Status] = 3 THEN 'Sent' ELSE NULL END AS StatusName FROM Users_TB CROSS JOIN Logs_TB` – Lukasz Szozda Oct 24 '15 at 20:45
  • 1
    So you need `case` then. – Martin Smith Oct 24 '15 at 20:45
  • Martin Smith ... I tried to use nested but I could not do it properly. – Ricky Suhendar Oct 24 '15 at 20:50
  • lad2025 ... Thanks ..I test your answer, Get this error 'An expression of non-boolean type specified in a context where a condition is expected, near 'THEN' – Ricky Suhendar Oct 24 '15 at 20:55
  • @lad2025 reopened as the OP appears to need more specific guidance than the dupe. – Martin Smith Oct 24 '15 at 21:00
  • 1
    Actually can you show us complete example data and desired results? The cross join of the two tables and the correlated sub query involving the same two tables looks odd to me. There might be a much easier way using a full outer join or something. – Martin Smith Oct 24 '15 at 21:07
  • @MartinSmith Thanks, and agree the CROSS JOIN is odd. – Lukasz Szozda Oct 24 '15 at 21:08

2 Answers2

3

You need to change your case and add more conditions:

SELECT ID, UserName,
    CASE 
        WHEN [Status] = 1 
             AND EXISTS (SELECT 1
                         FROM Logs_TB
                         WHERE Logs_TB.UserName = Users_TB.UserName) THEN 'Check'
        WHEN [Status] = 1 THEN 'In DB' 
        WHEN [Status] = 2 THEN 'Revision' 
        WHEN [Status] = 3 THEN 'Sent' 
        ELSE NULL 
    END AS StatusName
FROM Users_TB
CROSS JOIN Logs_TB

For performance reason is better to use EXISTS instead of comparing COUNT with 0.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
0

Try this 

DECLARE @Cnt INT 
SELECT @Cnt = COUNT(*) 
FROM Logs_TB 
WHERE Logs_TB.UserName = Users_TB.UserName 

SELECT 
       ID 
      ,UserName
       ,CASE WHEN [Status] = 1 
              THEN 
                    CASE WHEN @Cnt > 0 
                        THEN 'Check' 
                        ELSE 'In DB' 
                    END 
                WHEN [Status] = 2 THEN 'Revision' 
                WHEN [Status] = 3 THEN 'Sent' 
          END AS StatusName 
FROM Users_TB 
CROSS JOIN Logs_TB
RNA Team
  • 269
  • 1
  • 6