Exists question

[Digitalcandy02]

I am making a view and want to make a column that returns a boolean. The column will check for a record in another table, if it exists then true else false.


This is what I have and ofcourse it doesn't work but the linking is correct.

(Select Top 1 LotID From TravelerLot Where LotID = dbo.WorkOrderLot.ID)

All that code does is returns a NULL value if it doesn't exist and the LotID if it does. I'd rather it be a boolean.

 

[JohnDTampaBay]

Try this as a column definition:

SELECT
...Other columns...
, HasTravelerLot = CASE
WHEN (Select Top 1 LotID
From TravelerLot
Where LotID = dbo.WorkOrderLot.ID) IS NOT NULL
THEN 1
ELSE 0 END
FROM ...
WHERE ...

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[SemperFiDownUnda]

Another option that is slightly faster is this format. This is because EXISTS causes the actual subquery not to actually produce a result set.

SELECT CASE WHEN EXISTS (SELECT 1 FROM dbo.employee WHERE fname = 'Wayne')
THEN 1
ELSE 0
END

 

[SonOfEmidec1100]

My offering

SELECT CASE WHEN count(*) > 0 THEN 1 ELSE 0 END As SomeLots From TravelerLot Where LotID = dbo.WorkOrderLot.ID