×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

help with outer join

help with outer join

help with outer join

(OP)
I can't seem to get an outer join working the way I want.  Given the following:

1) the 'sample_custs' view will return a sample of ~100 customer IDs.
2) the 'customer_computed_value' table will have all the customer IDs.
3) the 'order_basket' table will only have a subset of the customer IDs.

I want the last statement (after the union) to return all the customer IDs, even if they are not in the 'order_basket' table.  This query is only returning the inner join.  Reversing the outer join to a 'right outer join' returns a null record.  A full outer join returns the null record and the inner join.  How can I get the customer IDs in the sample, but not in the order_basket table, to be listed?



with sample_custs as (
select a.customer_id
from customer sample(0.02) a)

select 'CCV',
       b.customer_id,
       b.c12_gross_amt
  from customer_computed_value b, sample_custs c
  where b.customer_id = c.customer_id
  
union

select x.*
from
(
select 'Order Basket',
       e.customer_id,
       sum(d.total_gross_amt)
  from order_basket d, sample_custs e
 where d.customer_id = e.customer_id
   and d.transaction_dt between to_date('16-SEP-2005', 'DD-MON-YYYY') - 365 and
       '16-SEP-2005'
 group by e.customer_id) x left outer join sample_custs y on x.customer_id = y.customer_id
;

RE: help with outer join

YOu second query should be

select x.type, y.customer_id, x.TGA
from sample_custs y left outer join
(
select 'Order Basket' Type,
       e.customer_id,
       sum(d.total_gross_amt) TGA
  from order_basket d, sample_custs e
 where d.customer_id = e.customer_id
   and d.transaction_dt between to_date('16-SEP-2005', 'DD-MON-YYYY') - 365 and
       '16-SEP-2005'
 group by e.customer_id) x
on y.customer_id = x.customer_id
;
 

RE: help with outer join

(OP)
got it to work, thanks!
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close