How can I produce yes and no value in column?

[jemz]

Hi how can I create sql statement for this that will produce yes and no value in alias column , I want to get all the list of user and user that is in the monitor table,and create alias column of monitor
if the user_id is in the monitor table then the value of this is “yes” if not “No”,
is this possible ?

Thank you in advance.

I have this table.

**USER-TABLE**

USER_ID      FNAME                    LNAME           USER_TYPE_ID            

100           Michelle                SMITH                1                        

101           LISA                    JOHNSON                2

103           SUSAN                   JONES                2

104           KAREN                    DAVIS                2

105           LISA                    GARCIA                2

106     JANE                    MILLER                2

107     ANNALIZA                ANDERSON            2

108     SHANE                    ALBAS                2

MONITOR-TABLE

ID     USER_ID        ADDED-BY

1        105                 100
             
2        108                 100        

how can I output like this ?

USER_ID      FNAME                 LNAME               MONITOR    

                  
101         LISA                    JOHNSON                NO

103         SUSAN                   JONES                NO

104         KAREN                    DAVIS                NO

105         LISA                    GARCIA                YES

106         JANE                    MILLER                NO

107         ANNALIZA                ANDERSON            YES

108         SHANE                    ALBAS                NO

[r937]

SELECT u.user_id , u.fname , u.lname , CASE WHEN m.user_id IS NULL THEN 'No' ELSE 'Yes' END AS monitor FROM users AS u LEFT OUTER JOIN monitors AS m ON m.user_id = u.user_id

[jemz]

@r937

SELECT u.user_id
, u.fname
, u.lname
, CASE WHEN m.user_id IS NULL
THEN ‘No’
ELSE ‘Yes’ END AS monitor
FROM users AS u
LEFT OUTER
JOIN monitors AS m
ON m.user_id = u.user_id

Thank you it works but I want also to put filter to get user_id in the monitor table where the added_by is 100 ?

I tried to put where u.user_id = 100
it only return 1 record

Thank you in advance.

[r937]

[quote=“jemz, post:3, topic:196212, full:true”]
I tried to put where u.user_id = 100
[/quote]put the condition in the ON clause, not the WHERE clause

[jemz]

you mean like this

     SELECT u.user_id
     , u.fname
     , u.lname
     , CASE WHEN m.user_id IS NULL
            THEN 'No'
            ELSE 'Yes' END AS monitor
  FROM user_table AS u
LEFT OUTER
  JOIN monitor_table AS m
    ON m.user_id = 100

I get all “No”

[r937]

i should have said add it to the ON clause, not put it in the ON clause

[jemz]

@r937,

I apologize, I can’t get what you mean…do I need to add another left join ?

Thank you in advance.

[jemz]

@r937,

I tried this

SELECT u.user_id
     , u.fname
     , u.lname
     , CASE WHEN m.user_id IS NULL
            THEN 'No'
            ELSE 'Yes' END AS monitor
  FROM user_table AS u
LEFT OUTER
  JOIN monitor_table AS m
    ON m.user_id = u.user_id
    AND
    u.user_id =100 

They will get all to “NO”.

[r937]

[quote=“jemz, post:8, topic:196212, full:true”]They will get all to “NO”.
[/quote]
please explain why you tried u.user_id=100 when you actually should be using m.added_by=100

[jemz]

Oh I apologize my mistake I always thinking the user_id I did not notice the added_by.
It works fine now thank you for correcting me.