Join a table only if result set > 0

I have a table A joined with a table B which give me a result set. I want to join a table C to the previous ones in order to restrict the result set. But in case there is no result with this join, I would like to have the same result set than before (without taking care开发者_StackOverflow of C).

Can you think of way to do that in SQL ?

---

SELECT *
FROM TableA
INNER JOIN TableB
    ON TableA.ID = TableB.TableAID
LEFT JOIN TableC
    ON TableC.ID = TableB.TableCID

This will return all rows from Tables A & B but only the rows from TableC where the ON criteria match.

Otherwise conditional joins don't really apply in standard SQL. If you are using SQL Server you can perform some stored procedure logic to check the results from TableC and if there are none then only get data from Table A & B. But this approach with be provider specific

---

Not possible with regular SQL since it involves logic.

Your best bet is to make a small script, e.g. (in pseudo code)

select * into #tmp from x inner join y inner join z where blabla;
if (exists (select * from #tmp))
BEGIN
  select * from #tmp
END
else
BEGIN
   select * from x inner join y where blabla;
END

Edit:

But if I were you, I would just always join with C using a LEFT JOIN, so you can see if the result was in one or the other result set...

e.g.

select x.*, y.*, case when z.id is null then 0 else 1 end from x inner join y left join z on blabla where blabla;

But that of course assumes you are able to alter the code path that reads the result.

---

I see a problem in the LEFT/OUTER JOIN methods. If you do it you could get some results that are in A and B but not in C. If I understand well the porpouse is join AB with C, I mean the result when crossing with C must include the three restrictions. So the @Cine solution is the apropiate to this case.