SQLTeam.com | Weblogs | Forums

How to show a 1 or 0 in select statement


#1

Hi,

I've got 3 types of user in separate tables - recruiter / assessor and admin. They refer to a SystemUser table. A SystemUser can be 1 or more type of user.

I want to display it in the diagram below.

I joined the tables but I want a 1 or 0 to show up but don't know how. At the moment its either a null or SystemUserId.

SELECT
su.firstname,
su.lastname,
su.email,
su.systemuserid,
U.Systemuserid as Admin,
A.systemuserid as Assessor,
R.systemuserid as Recruiter
FROM SystemUsers SU
LEFT JOIN Users U ON SU.SystemUserId = U.SystemUserID
LEFT JOIN Assessor A on A.SystemUserId = SU.SystemUserId
LEFT JOIN Recruiters R on R.SystemUserId = SU.SystemUserId

<p:metadata><p:property name="box"></p:property><p:property name="useHtmlContent"></p:property><p:property name="fixedHeaderHeight"></p:property><p:property name="padding"></p:property><p:property name="headerHeight"></p:property><p:property name="h0"></p:property><p:property name="h1"></p:property><p:property name="h2"></p:property><p:property name="h3"></p:property><p:property name="h4"></p:property><p:property name="h5"></p:property><p:property name="h6"></p:property><p:property name="h7"></p:property><p:property name="h8"></p:property><p:property name="h9"></p:property><p:property name="h10"></p:property><p:property name="h11"></p:property><p:property name="content"></p:property><p:property name="headerFont"></p:property><p:property name="textFont"></p:property><p:property name="textAlign"></p:property><p:property name="customStyle"></p:property><p:property name="textColor"></p:property><p:property name="fillColor"></p:property><p:property name="headerTextColor"></p:property><p:property name="headerBackground"></p:property><p:property name="strokeColor"></p:property><p:property name="strokeStyle"></p:property></p:metadata>




First Name
Surname
Email
Recruiter
Assessor
Admin
Paul
Jones
Delete
Cell content 2
Delete
Cell content 3
Delete




#2

hi

one thing you can do is ..use case statement
case when leftjoincolumn is null then 0 else 1 end

hope it helps
:slight_smile:
:slight_smile: