Home » SQL & PL/SQL » SQL & PL/SQL » LEFT JOIN
LEFT JOIN [message #209481] Fri, 15 December 2006 02:38 Go to next message
juicyapple
Messages: 92
Registered: October 2005
Member
I have created two tables

TableA (MachineNo)
TableB(DocNum, MachineNo)

Select * from TableA

MachineNo
---------
A001
A002
A003
A004
A005

Select * from TableB

DocNum MachineNo
------ ---------
D001 A001
D002 A005

Select TableA.MachineNo, TableB.DocNum
FROM TableA Left Join TableB ON TableA.MachineNo = TableB.MachineNo

MachineNo DocNum
--------- ------
A001 D001
A002 Null
A003 Null
A004 Null
A005 D002

I have problem if I want to filter docnum in tableB (where DocNum = 'D001') and still list out all the MachineNo. Any help is appreciated.
Re: LEFT JOIN [message #209486 is a reply to message #209481] Fri, 15 December 2006 02:50 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
So you don't want a left join after all?

What you are doing is the following:
- Give me all records from TableA.
- Give me related records from TableB if any, if not just display the TableA data. That's your left join.

Now you add an extra constraint that Oracle needs to display the TableB data only if the docnum is 'D001'. But the left join remains. What happens if you change that into an INNER JOIN?

MHE
Re: LEFT JOIN [message #209488 is a reply to message #209486] Fri, 15 December 2006 02:56 Go to previous messageGo to next message
juicyapple
Messages: 92
Registered: October 2005
Member
If change to inner join

SELECT TABLEA.MACHINENO, TABLEB.DOCNUM
FROM TABLEA
INNER JOIN TABLEB ON TABLEA.MACHINENO = TABLEB.MACHINENO
WHERE TABLEB.DOCNUM='D001'

it returns

MachineNo DocNum
--------- ---------
A001 D001

but how to get all machine no listed out??
Re: LEFT JOIN [message #209489 is a reply to message #209488] Fri, 15 December 2006 02:58 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I'm sorry, I must have misread the question. So, you want to display all the machineno but only docnum 'D001'? Perhaps you could post the desired output?

Edit: never mind. Glad you figured it out yourself.

MHE

[Updated on: Fri, 15 December 2006 02:59]

Report message to a moderator

Re: LEFT JOIN [message #209490 is a reply to message #209481] Fri, 15 December 2006 02:58 Go to previous message
juicyapple
Messages: 92
Registered: October 2005
Member
haha, I found it...
should be

SELECT TABLEA.MACHINENO, TABLEB.DOCNUM
FROM TABLEA
LEFT JOIN TABLEB ON TABLEA.MACHINENO = TABLEB.MACHINENO AND TABLEB.DOCNUM='D001'

Thanks.
Previous Topic: Need Help plz
Next Topic: URGENT - display count for criteria....
Goto Forum:
  


Current Time: Sun Dec 04 19:10:01 CST 2016

Total time taken to generate the page: 0.04843 seconds