Re: newbie select and join question

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
>
Received on Tue Oct 04 2005 - 14:51:42 CDT