Conditional Join possible in SQL Server 2005?

Question

I need to write a select query that joins the tables based on a condition (in this case, based on a value in one of the columns). I would like to do something like this:

SELECT *
  FROM TableA
       INNER JOIN TableB ON (TableA.Column1 = TableB.Column1 OR TableA.Column1 = 0) -- Does not work!

Answer 1

I'm not exactly sure what you are doing but it seems like you are looking for an outer join:

SELECT *
FROM TableA LEFT OUTER JOIN TableB ON TableA.Column1 = TableB.Column1
WHERE TableB.Column1 IS NOT NULL
   OR TableA.Column1 = 0

Answer 2

This should achieve what you need:

SELECT *
FROM TableA A
WHERE A.Column1 = 0
   OR EXISTS(
            SELECT B.Column1
            FROM TableB B
            WHERE B.Column1 = A.Column1
            );

Answer 3

If you want rows from Table A and rows from Table B, just use two select statements separated by a UNION command.