newbie select and join question

Sorry, this I am sure is a dumb question, but an hour of google searches did not yield an answer to a newbie DB guy... I've tried to make my example data really super simple by leaving off everything that was not necessary to ask my question clearly, hopefully it makes sense.
Any pointers or advice would be really helpful.

So let's say I have two tables in an old oracle 8i db (which is true)...

one has options and costs for that option

cost_table
option cost
black 10
white 5
red 9

other has orderid, options for that order and number of those options requested

units_table
id option units
123 black 2
123 white 1
456 white 3
456 red 2
789 black 1

I am trying to write a select statement that will display for any given order ID ALL of the options listed in the cost_table, as well as the current number of units for any option that is already in the units_table for that order id...

What I can do succesfully is:

select a.option, b.units from cost_table a, units_table b where a.option = b.option(+);

which of course returns me all of the options from the cost_table and the units value from the units_table but for EVERY order, beacuse there is no limiting clause in the statement.

So, I try four hundred variations of things the best one being:

select a.option, b.units from cost_table a, units_table b where a.option = b.option(+) and b.id=123;

But this only returns me the options from cost_table that already have a value for units in the units_table...

black 2
white 1

the result set I really need would look like:

black 2
white 1
red null (or whatever)

I've tried things like ...and b.id=123(+) but that's not valid.

I think what I need to do is a nested select statement or something, but I really can't seem to figure that out...

Any help or pointers to help would be super appreciated. Regards,
ct Received on Tue Oct 04 2005 - 14:11:06 CDT