SQL - Left Join problem...

Question

SELECT 
  tb1.booking_ref, tb1.investor, tb2.cost, tb3.product 
FROM 
  tb1, tb3 LEFT JOIN tb2 
ON
  tb1.booking_ref = tb2.booking_ref 
AND 
  tb1.investor = tb2.investor 
AND 
  tb1.investor = '12345'
WHERE
  tb1.location = tb3.location

The above query errors because of the references to tb3 - it works great without them.

Does anyone have any idea why??

Answer 1

Instead of in the WHERE clause, add tb1.location = tb3.location to an ON/AND clause.

Answer before the question update:
Yep it would.
Where did you state the relationship between table tb3 and either of tb1, tb2? For joins, you need to have a relationship between certain columns among these tables.

HTH!

Answer 2

SELECT 
  tb1.booking_ref, tb1.investor, tb2.cost, tb3.product
FROM 
  tb1
      inner join tb3
          on tb1.location = tb3.location
      left join tb2 
          on tb1.booking_ref = tb2.booking_ref
              and tb1.investor = tb2.investor 
WHERE tb1.investor = '12345'

Answer 3

This might help you:

SELECT t1.booking_ref, t1.investor, t.cost, t.product
FROM tb1 t1
CROSS APPLY(
    SELECT t2.cost, t3.product 
    FROM tb3 t3 
    LEFT JOIN tb2 t2 ON (t1.booking_ref = t2.booking_ref  
                    AND t1.investor = t2.investor  
                    AND  t1.investor = '12345')
) AS t

PS:- you need at least SQL Server 2005 for this.