Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

join, union, and order

609797Nov 21 2007 — edited Nov 21 2007
I've been looking at some of the other posts on this and have not seen my exact problem (granted, I have not looked at all of the posts, as there are quite a few, so please forgive me if this has been covered before).

I have two tables, A & B, that I want to join. They have different data. They both contain fields 1 & 2 which I want to sort by. There are records in A that are not in B, and vice versa.

I've tried all sorts of things. The closest I've come to what I'm looking for looks like this:

select * from A, B
where A1 = B1 (+)
union
select * from A, B
where A1 (+) = B1
order by A2, A1

This gives me all the records I'm looking for, but not sorted in the right order. The problem is with records that are in B, but not in A. When sorting by A2, A1 the fields for records in B and not in A are null and appear at the end.

And yes, I've tried using numbers instead of field names in the order by. Didn't work either. I also tried using NVL to tell it to use B2 if A2 is null.

Right, left, and outer join do not work on the version I'm using.

Any ideas??
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 19 2007
Added on Nov 21 2007
10 comments
543 views