If Else Statement within an inner join

I have a table jobs with a column StartDate that is often null. If it is not null this is what I want to inner join with a table quarters on, otherwise I want to inner join on some other conditions. This is sort of what I want:

INNER JOIN Quarters q
ON  (IF j.StartDate IS NOT NULL (j. StartDate BETWEEN GETDATE() and q.EndDate)
     ELSE **Some o开发者_C百科ther condition**)

The error that comes up when this is run is that there is incorrect syntax near the keyword 'IF'

Does anyone know the correct syntax for this?

Thanks in advance for your help!

---

INNER JOIN Quarters q ON
    (j.StartDate IS NOT NULL AND j. StartDate BETWEEN GETDATE() and q.EndDate)
OR
    (j.StartDate IS NULL AND **Some other condition**)

---

The easiest way to handle this is just to treat it as a logical operation:

ON    (j.StartDate IS NOT NULL 
       and j. StartDate BETWEEN GETDATE() and q.EndDate)
   OR (some other condition)

---

Try

CASE WHEN j.StartDate IS NULL THEN somevalue ELSE some_other_value END

the syntax is

CASE WHEN some_boolean_expression THEN some_value_column_or_expression
   WHEN some_boolean_expression THEN some_value_column_or_expression
... (repeat when as many times you need it)
   ELSE some_value_column_or_expression
END

Note: Else can be ommited.

also see the links

http://www.craigsmullins.com/ssu_0899.htm

http://msdn.microsoft.com/en-us/library/ms181765.aspx