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

You need a join condition between tb1 and tb3. How are they related? Also, based off your [previous question](http://stackoverflow.com/questions/3865680/sql-select-where-matching-record-exists-and-no-matching-record), the `tb1.investor = '12345'` belongs in the `WHERE` clause, not as part of the join between tb1 and tb2. — Joe Stefanelli, Oct 05 '10 at 17:02
Please don't use the old join sintax!!!!! When you do a migration sql from 2005 and beyond will have problems. — Gabriel Guimarães, Oct 05 '10 at 17:25

score 7 · Accepted Answer · answered Oct 05 '10 at 17:06

7

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'

answered Oct 05 '10 at 17:06

Joe Stefanelli

132,803
19
237
235

^^ Shouldn't the WHERE condition be included as an AND clause as well, to make the query work on the maximum filtered data set. – Dienekes Oct 05 '10 at 17:11

score 0 · Answer 2 · edited Mar 02 '20 at 13:14

0

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.

edited Mar 02 '20 at 13:14

halfer

19,824
17
99
186

answered Oct 05 '10 at 17:04

Dienekes

1,548
1
16
24

score 0 · Answer 3 · edited Oct 05 '10 at 19:47

0

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.

edited Oct 05 '10 at 19:47

A-K

16,804
8
54
74

answered Oct 05 '10 at 17:12

TheVillageIdiot

40,053
20
133
188

SQL - Left Join problem

3 Answers3