bdj wrote:
> Hi!
> 1.) You could insert in units_table: 123 red 0.
> or
> 2.) What if you had an view that shows you, what is "missing" in unit_table:
> create view missing as
> select id from id_with_all_colors ac
> where not exists (select null from your_own_outer_join y where
> y.id=ac.id)
>
> create view id_with_all_colors as
> select uu.id, c.option
> from (select distinct u.id id from units_table u) uu, cost_table c
> -- no where-clause, cartesian join here!
> ;
>
> Then you will be able to make an union of your_own_outer_join and what is
> missing.
>
> Greetings
> Bjørn
>
>
>
>
> "DravenStone" <chiptricks_at_gmail.com> skrev i en meddelelse
> news:1128453066.545958.261460_at_f14g2000cwb.googlegroups.com...
>
>>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
>>
>
Hi Bjørn,
Sounds like you need to do something like this:
select a.option
,b.units
from cost_table a
,units_table b
where a.option = b.option(+)
and b.id = 123
union
select c.option
,0
from cost_table c
where c.option not in (select d.option
from units_table d
where d.id = 123);
Probably not a miracle in terms of execution speed, but it would work.
Hope this helps,
Ruud de Koter.
Received on Tue Oct 04 2005 - 15:43:36 CDT
