Hi pete try the following:

Select table.*, 
CASE table2.field2 WHEN IS NULL THEN CAST(0 as int) ELSE CAST(1 AS int) END AS field2 
from table1 
LEFT JOIN table2 ON table2.ifkid = table1.id AND table2.type = 'X'

Just pseudo code but you should get the idea ;)

>Hello All, I need to include a derived field in a view based on whether a certain value is present in another table ( does that make sense ? ). The problem is, the table in question can have more than one record but I'm only interested in a certain type, any ideas as to how I can do this ?
>
>pseudo code
>
>Select * from table1 and if there is a record of type 'X' in table2 set field2 to 1 else set field2 to 0