CODEWITHSUNDEEP
sqlsql-server
Tom
Tom

Reputation: 12988

SQL - Left Join problem

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??

Upvotes: 4

Views: 1222

Answers (3)

Dienekes
Dienekes

Reputation: 1548

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

Answer before the question update: yes, 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.

Upvotes: 0

TheVillageIdiot
TheVillageIdiot

Reputation: 40497

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.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135739

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'

Upvotes: 7

Related Questions