Hi,

I have the following SQL written in Oracle :

SELECT DISTINCT A.* FROM MyTABLEA A , MyTABLEB B
WHERE A.ID = '77787'
AND A.ID =B.ID(+)
AND A.MAIN = B.MAIN(+)
AND A.NO = B.NO(+)
AND A.EQP = B.EQP(+)
AND A.JOB_ID = B.JOB_ID(+)

my purpose is to get all record fro ID =7787 from MYTABLEA even if no matching record in MYTABLEB

i tried

SELECT DISTINCT A.* FROM MyTABLEA A LEFT OUTER JOIN MyTABLEB B
ON A.ID = '77787'
AND A.ID =B.ID
AND A.MAIN = B.MAIN
AND A.NO = B.NO
AND A.EQP = B.EQP
AND A.JOB_ID = B.JOB_ID

but it seems to return ALL records from MyTABLEA and not just limited to the specified records from MYTABLEA

please advise

You need to find a SQL manual on how to do an outer join. You must separate the join predicates (ON) from the predicates you want to filter the rows (WHERE) in the first table.

So it looks to me like A.ID = '77787' should be in a WHERE clause of the outer join, and not used in the ON clause.

As Marcus says above you are not limiting what you want from the A table. You are only limiting when you perform the outer join to the B table, so that the only time you will see data that exists in the B table is when the A.ID is '77787'.

Dave