Re: Anyone good with Joins?

On Apr 9, 11:04 am, patrick <pgov..._at_u.washington.edu> wrote:
> On Apr 9, 8:42 am, Mtek <m..._at_mtekusa.com> wrote:
>
>
>
> > On Apr 9, 10:28 am, Mtek <m..._at_mtekusa.com> wrote:
>
> > > Hi,
>
> > > I'm thinking that this should be an outer join, but I am looking for
> > > some decent examples.
>
> > > In this query, the conditions are the same. However, the second query
> > > contains one extra table/condition/column not in the first query.
> > > Right now I am getting duplicates which would be the result of both
> > > queries........
>
> > > Any thoughts? If I find a good example searching the net it'd be
> > > great. but I thought I'd also ask some of the experts here.
>
> > > Thank you,
>
> > > SELECT TO_CHAR(co.date_entered, 'DD-MON-RRRR HH:MI:SS AM')
> > > date_entered, p.subproduct_id, p.code,
> > > p.price, co.customer_id, ol.status, co.confirm, NULL adid
> > > FROM customer_order co, order_line ol, product p
> > > WHERE co.order_id = ol.order_id AND ol.product_id = p.product_id
> > > AND co.date_entered >= TRUNC(SYSDATE) - 7 AND p.subproduct_id IN
> > > (SELECT newsletter_id FROM product_group)
> > > UNION
> > > SELECT TO_CHAR(co.date_entered, 'DD-MON-RRRR HH:MI:SS AM')
> > > date_entered, p.subproduct_id, p.code,
> > > p.price, co.customer_id, ol.status, co.confirm, ss.adid
> > > FROM customer_order co, order_line ol, product p,
> > > data_holder.shopcart_sessions ss
> > > WHERE co.order_id = ol.order_id AND ol.product_id = p.product_id
> > > AND ol.order_id = ss.order_id AND co.date_entered >= TRUNC(SYSDATE)
> > > - 7 AND p.subproduct_id IN
> > > (SELECT newsletter_id FROM product_group) ORDER BY date_entered
> > > DESC;
>
> > I came up with this join, but it yields different results:
>
> > SELECT TO_CHAR(co.date_entered, 'DD-MON-RRRR HH:MI:SS AM')
> > date_entered, p.subproduct_id, p.code,
> > p.price, co.customer_id, ol.status, co.confirm, ss.adid,
> > ol.order_id, co.customer_id
> > FROM engine.customer_order co
> > JOIN engine.order_line ol ON (co.order_id = ol.order_id)
> > JOIN engine.product p ON (ol.product_id = p.product_id)
> > JOIN data_holder.shopcart_sessions ss ON (ol.order_id = ss.order_id)
> > WHERE co.date_entered >= TRUNC(SYSDATE) - 7 AND p.subproduct_id IN
> > (SELECT newsletter_id FROM product_group)
> > ORDER BY co.date_entered;- Hide quoted text -
>
> > - Show quoted text -
>
> You might try
> SELECT TO_CHAR(co.date_entered, 'DD-MON-RRRR HH:MI:SS AM'),
> p.subproduct_id, p.code,
> p.price, co.customer_id, ol.status, co.confirm, ss.adid
> FROM customer_order co, order_line ol, product p,
> data_holder.shopcart_sessions ss
> WHERE co.order_id = ol.order_id
> AND ol.product_id = p.product_id
> AND co.date_entered >= TRUNC(SYSDATE)- 7
> AND p.subproduct_id IN (SELECT newsletter_id FROM product_group)
> and ss.order_id(+) = ol.order_id
> ORDER BY date_entered DESC;
>
> ====>Patrick

Patrick,

This looks like it is working. I'll have the other customers check it out.

Is all you did was combine them and add the outer join on the table which contains the differing column??

John Received on Wed Apr 09 2008 - 11:14:30 CDT