Re: Anyone good with Joins?

On Apr 9, 10:28 am, Mtek <m...@[EMAIL PROTECTED]
> 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;