Geek Answers Handbook

SQL chooses where the corresponding record exists and there is no corresponding record

I need to cross reference 2 tables.

inside tb1 is booking_ref, investor

inside tb2 - booking_ref, investor, value

The problem is that at no cost the record is not created in table 2

So I have the following request ...

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 orders that have suitable booking_ref in tb2, but I also need to display orders where there is no suitable booking_ref

any ideas

+2
source share
7 answers

You need 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''
+6
source

LEFT JOIN

I was about to publish an example, but several people beat me.

However, only FYI, your message uses the Implicit INNER JOIN . The answers / examples use what is called the Explicit JOIN .

Explicit vs implicit SQL join

I'm used to always use the Explicit JOIN syntax, although for INNER JOIN s it is more confusing, but more consistent because you need to use it for LEFT JOIN and FULL OUTER JOIN s.

Btw, LEFT JOIN is synonymous with LEFT OUTER JOIN, but the exact syntax depends on your RDBMS. And RIGHT JOINs are technically redundant because you can still use the LEFT JOIN keywords and just reorder your tables in your SQL.

+4
source 
 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'
+1
source

LEFT JOIN - 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'
0
source

Try the following:

 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)
0
source

Take a look at the various types of SQL statements that offer you such as Left Join, Right Join, Full Join. There is a good link on this site to help you get started: http://www.w3schools.com/sql/sql_join.asp . Understand that each of the connections will help you complete, and as soon as you do, I believe that you will have your answer.

0
source

The server is not specified ... but if it is in Oracle before 8i, I do not think that the syntax of the internal or left connection will work. If you are in early versions of the oracle, use (+) 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 this is correct ... failed to verify)

0
source

More articles:


All Articles