SQL select where matching record exists and no matching record

Question

I need to cross reference 2 tables.

within tb1 is booking_ref, investor

within tb2 is booking_ref, investor, cost

The trouble is if there in no cost, no record is created in table 2

So I have the following query...

SELECT 
  tb1.booking_ref, tb1.investor, tb2.cost 
FROM 
  tb1, tb2 
WHERE 
  tb1.booking_ref = tb2.booking_ref 
AND 
  tb1.investor = tb2.investor 
AND 
  tb1.investor = ''12345''

this displays all bookings where there is a matching booking_ref in tb2 but I also need to display bookings where there is not a matching booking_ref

any ideas??

score 6 · Accepted Answer · answered Oct 05 '10 at 16:27

6

You want a left join in this case.

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

answered Oct 05 '10 at 16:27

Joe Stefanelli

132,803
19
237
235

+1 [BANG]Answer posted[/BANG]. Might want an isnull or coalesce on the cost. – Oct 05 '10 at 16:30
or if you ONLY want the cases where the tb2 record is missing, add the WHERE conditions "and tb2.booking_ref IS NULL" – Bill Oct 05 '10 at 16:34

score 4 · Answer 2 · edited May 23 '17 at 11:55

4

LEFT JOIN

I was going to post an example, but several people beat me to it.

However, just an FYI, your post is using the Implicit INNER JOIN syntax. The answers/examples are using what's known as the Explicit JOIN syntax.

Explicit vs implicit SQL joins

I am in the habit of always using the Explicit JOIN syntax, even though for INNER JOINs, it's more confusing looking, but it's more consistent, because you need to use it for LEFT JOINs and FULL OUTER JOINs.

Btw, LEFT JOIN is synonymous with LEFT OUTER JOIN, but the exact syntax depends on your RDBMS. And RIGHT JOIN is technically superfluous, because you can still use LEFT JOIN keywords and just reverse the order of your tables in your SQL.

edited May 23 '17 at 11:55

Community

1
1

answered Oct 05 '10 at 16:36

JohnB

18,046
16
98
110

+1 agree with misnomer, good basics, I'll disagree with you however, I think that the INNER JOIN syntax is clearer – Nathan Koop Oct 05 '10 at 16:53
+1 for always using explicit joins. However I too think that the explicit join syntax is (much) clearer. – Paul Groke Oct 05 '10 at 16:56
Thanks guys! What I meant was, the implicit `JOIN` syntax might make more sense to and be useful to someone who hasn't learned explicit `JOIN` syntax yet. However, I definitely feel that minimal SQL skills should include understanding all the "ins" and "outs" of relational `JOIN`s, and therefore explicit `JOIN` syntax. I also prefer the explicit syntax for all my queries. – JohnB Oct 05 '10 at 22:07

score 1 · Answer 3 · answered Oct 05 '10 at 16:29

1

select t1.booking_ref, t1.investor, t2.cost
from tb1 t1
left join tb2 t2 
     on t1.booking_ref = t2.booking_ref
     and t1.investor = t2.investor
where t1.investor = '12345'

answered Oct 05 '10 at 16:29

Fosco

38,138
7
87
101

Vishal · Answer 4 · 2010-10-05T16:50:34.527

0

LEFT JOIN is your man-

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

edited Oct 05 '10 at 16:50

answered Oct 05 '10 at 16:30

Vishal

12,133
17
82
128

fyi - outer is implied... you can save a few keystrokes. – Fosco Oct 05 '10 at 16:39
You have no `ON` condition specified for your left join. You'd get a syntax error on this query as written. – Joe Stefanelli Oct 05 '10 at 16:47
Thanks for correcting...copy pasted his example while rushing through it..didn't realise it...you beat me to it anyways... – Vishal Oct 05 '10 at 16:52

score 0 · Answer 5 · answered Oct 05 '10 at 16:32

0

Try this:

SELECT  tb1.booking_ref, 
          tb1.investor, 
          tb2.cost 
FROM      tb1 left outer join
          tb2 on tb1.booking_ref = tb2.booking_ref
where    tb1.investor = tb2.investor
and    tb1.investor = '12345'
and (       tb1.booking_ref = tb2.booking_ref
        or     tb2.booking_ref is null)

answered Oct 05 '10 at 16:32

MLT

524
7
19

No. `tb1.investor = tb2.investor` is part of the join condition between the two tables. Placing this condition in the where clause as you have done would make your left join behave like an inner join. – Joe Stefanelli Oct 05 '10 at 16:45
yes, on the investor, I thought that was what he wanted from the question wording at the end :S – MLT Oct 06 '10 at 10:18

score 0 · Answer 6 · answered Oct 05 '10 at 16:33

Take a look at the different types of Joins SQL offers you such as Left Join, Right Join, Full Join. This site has a good reference to help you get started: http://www.w3schools.com/sql/sql_join.asp. Understand what each of the joins will help you accomplish, and once you do, I believe you will have your answer.

score 0 · Answer 7 · answered Oct 05 '10 at 16:56

Server isn't stated...but if he's in Oracle prior to 8i, I don't think inner or left join syntax will work. If you are in early oracle versions, use the (+) for outer joins.

SELECT
tb1.booking_ref, tb1.investor, tb2.cost
FROM
tb1, tb2
WHERE
tb1.booking_ref = tb2.booking_ref
AND
tb1.investor = tb2.investor(+) AND
tb1.investor = ''12345''

(I think thats right...unable to verify)

SQL select where matching record exists and no matching record

7 Answers7

Linked