Can i show defaults in view that joins two tables, for records that don't have matching record in 2nd table?
Posted on 2007-08-08
I have two tables, tblA and tblB. TblB has a FK to tblA, but not every record in tblA will have a corresponding record in tblB. In tblB, defaults are defined on certain columns.
If i create a view, joining tblA and tblB, i'd like to get the default values defined in tblB if there is no record in tblB. I know i can do a isNULL(tblB.fieldB1, 'the default value') in the view, but i'd have to change the value in the isNull statement if i ever gonna change the default value in tblB. So is there a way to show the default values in the view?
TO clarify, here some tablestructures. I left out the types cause they are not relevant for the question.
fieldB2 default ('def2')
view as i'd like it to work:
select * from tblA left join tblB on tblA.A_ID = tblB.FID_tblA.
I know I can do this, but i'd like an alternative to that.
select A.A_ID, A.FieldA1, isNULL(B.fieldB1, 'def1') as fieldB1, etc
Hope the question makes sense.