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;

On Apr 9, 10:28 am, Mtek <m... (AT) mtekusa (DOT) com> wrote:

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;

On Apr 9, 10:28 am, Mtek <m... (AT) mtekusa (DOT) com> wrote:

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;

On Apr 9, 10:28 am, Mtek <m... (AT) mtekusa (DOT) com> wrote:

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;

On Apr 9, 10:28 am, Mtek <m... (AT) mtekusa (DOT) com> wrote:

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;

On Apr 9, 8:42*am, Mtek <m... (AT) mtekusa (DOT) com> wrote:
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

On Apr 9, 8:42*am, Mtek <m... (AT) mtekusa (DOT) com> wrote:
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

On Apr 9, 8:42*am, Mtek <m... (AT) mtekusa (DOT) com> wrote:
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

On Apr 9, 8:42*am, Mtek <m... (AT) mtekusa (DOT) com> wrote:
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

On Apr 9, 11:04 am, patrick <pgov...@u.washington.edu> wrote:
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