SQL Query - Table Joining Problems

I am having some serious problems wrapping my head around how to build a proper query for my situation.. Pretty sure it depends on joining the tables properly but I cant seem to fi

Solution 1:

I used INNER JOIN for status, assuming that every main record refers to an existing status record. If that is not the case, you may want to change it to a LEFT JOIN.

For the WhenDate, you can just left join Secondary. If a record is found, you can compare against Secondary.WhenDate, otherwise, check against 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 ActualWhenDate
FROM
  Main m
  INNER JOIN Status st ON st.ID = m.StatusID
  LEFT JOIN Secondary s ON s.MainID = m.ID
WHERE
  ( 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 ...