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

Answer 1

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

Answer 2

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'

Answer 3

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'

Answer 4

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)

Answer 5

Hi Tom, 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.

Answer 6

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.

Answer 7

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)