MS SQL SERVER LEFT OUTER JOIN query

  • Thread starter Thread starter deven
  • Start date Start date
D

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
 
Hi,

Try with this:
CAST (CASE WHEN Job.projectId is null then 0 else 1 end as bit)
 
deven said:
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
Click to expand...

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
 
Back
Top