MS SQL SERVER LEFT OUTER JOIN query

[deven]

In MS sql Server I'm doing left Outer Join from say Table1
to Table2, I'm only selecting FK column of Table2
say "Table2.relFkCol", If there are no matching records in
Table2 I'm getting Null for "Table2.relFkCol", and for
matching records I get positive Integer value. I want to
write query such that I get Boolean column, instead of
Null or Positive Value.
my present query is like this

SELECT Project.*,ISNULL(Job.projectId, 0) AS
jobTicketCreated
FROM Project LEFT OUTER JOIN
Job ON Project.projectId = Job.projectId

Please can anybody help me here.
thanks

 

[Miha Markic]

Hi,

Try with this:
CAST (CASE WHEN Job.projectId is null then 0 else 1 end as bit)

 

[Mythran]

In MS sql Server I'm doing left Outer Join from say Table1
to Table2, I'm only selecting FK column of Table2
say "Table2.relFkCol", If there are no matching records in
Table2 I'm getting Null for "Table2.relFkCol", and for
matching records I get positive Integer value. I want to
write query such that I get Boolean column, instead of
Null or Positive Value.
my present query is like this

SELECT Project.*,ISNULL(Job.projectId, 0) AS
jobTicketCreated
FROM Project LEFT OUTER JOIN
Job ON Project.projectId = Job.projectId

Please can anybody help me here.
thanks

SELECT [Project].*, IIF([Job].[projectId] IS NULL, 0, 1) AS [jobTicketCreated]
FROM [Project], [Job] WHERE [Project].[projectId] = [Job].[projectId]

try that, fix it up...whatever, may or may not be what ya want

Also, I may be wrong about IIF, don't remember if it's supported in SqlServer, I
believe it is.


Mythran

 

[deven]

Miha Markic - RightHand .NET consulting & software
Thanks. That worked fine!