我使用
INNER JOIN状态作为假设每个主记录都引用一个现有的状态记录。如果不是这种情况，您可能需要将其更改为
LEFT JOIN。
对于WhenDate，您只需离开即可加入Secondary。如果找到一条记录，则可以与Secondary.WhenDate进行比较，否则，可以与Main.WhenDate进行比较。
SELECt m.ID as MainID, m.WhenDate as MainWhenDate, m.InfoText, m.StatusID, st.StatusText, m.TypeID, s.WhenDate as SecondaryWhenDate, CASE WHEN s.MainID IS NULL THEN m.WhenDate ELSE s.WhenDate END AS ActualWhenDateFROM Main m INNER JOIN Status st ON st.ID = m.StatusID LEFT JOIN Secondary s ON s.MainID = m.IDWHERe ( s.MainID IS NULL AND m.WhenDate = <YourDate> OR s.MainID IS NOT NULL AND s.WhenDate = <YourDate> ) AND TypeId = <TypeFilter> AND ... other filters, if you need any ...
