Anyone good with Joins?

#11

On Apr 9, 11:04 am, patrick <pgov...@u.washington.edu> wrote:

Quote:

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

On Apr 9, 10:28 am, Mtek <m... (AT) mtekusa (DOT) 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

#12

On Apr 9, 11:04 am, patrick <pgov...@u.washington.edu> wrote:

Quote:

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

On Apr 9, 10:28 am, Mtek <m... (AT) mtekusa (DOT) 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

#13

On Apr 9, 11:04 am, patrick <pgov...@u.washington.edu> wrote:

Quote:

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

On Apr 9, 10:28 am, Mtek <m... (AT) mtekusa (DOT) 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

#14

On Apr 9, 11:04 am, patrick <pgov...@u.washington.edu> wrote:

Quote:

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

On Apr 9, 10:28 am, Mtek <m... (AT) mtekusa (DOT) 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,

Since you seem to be the genius at joins, do you think this query can
be shortened?

SELECT c.email || '|' || ca.first_name || '|' || ca.last_name ||
'|' || 'ZACKS' || '|' ||
TO_CHAR(c.date_registered, 'mmddrr hh24:mi:ss') || '|' ||
TO_CHAR(TRUNC(SYSDATE-1),'MM-DD-YYYY') || '|' ||
v.adid || '|' || cd.day_phone line, 'HOUSE' list_type
FROM customer c, customer_account ca, visitor v,
subscr_email_product s, customer_address cd
WHERE c.date_registered BETWEEN p_start_date AND p_end_date
AND c.customer_id = ca.customer_id(+)
AND c.customer_id = s.customer_id
AND c.customer_id = v.customer_id
AND c.customer_id = cd.customer_id(+)
AND s.email_product_id = 'HL'
AND email NOT IN (
SELECT email
FROM customer c, customer.subscriptions s, customer.product p,
customer_address ca, customer_account ct, visitor v
WHERE ca.address_type_id = 1
AND s.status = 1
AND s.sell_rep_id IN (201, 202)
AND p.produst_id = 1
AND TRUNC(start_date) BETWEEN p_start_date AND p_end_date
AND p.produst_id = s.produst_id
AND c.customer_id = s.customer_id
AND c.customer_id = ca.customer_id
AND c.customer_id = ct.customer_id
AND c.customer_id = v.customer_id (+))
UNION
SELECT c.email || '|' || ca.first_name || '|' || ca.last_name ||
'|' || 'ZACKS' || '|' ||
TO_CHAR(c.date_registered, 'mmddrr hh24:mi:ss') || '|' ||
TO_CHAR(TRUNC(SYSDATE-1),'MM-DD-YYYY') || '|' ||
v.adid || '|' || cd.day_phone line, 'AMERITRADE' list_type
FROM customer c, customer_account ca, visitor v,
subscr_email_product s, customer_address cd
WHERE c.date_registered BETWEEN p_start_date AND p_end_date
AND c.customer_id = ca.customer_id(+)
AND c.customer_id = s.customer_id
AND c.customer_id = v.customer_id
AND c.customer_id = cd.customer_id(+)
AND s.email_product_id = 'HL'
AND email IN (
SELECT email
FROM customer c, customer.subscriptions s, customer.product p,
customer_address ca, customer_account ct, visitor v
WHERE ca.address_type_id = 1
AND s.status = 1
AND s.sell_rep_id IN (201, 202)
AND p.produst_id = 1
AND TRUNC(start_date) BETWEEN p_start_date AND p_end_date
AND p.produst_id = s.produst_id
AND c.customer_id = s.customer_id
AND c.customer_id = ca.customer_id
AND c.customer_id = ct.customer_id
AND c.customer_id = v.customer_id (+));

#15

On Apr 9, 11:04 am, patrick <pgov...@u.washington.edu> wrote:

Quote:

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

On Apr 9, 10:28 am, Mtek <m... (AT) mtekusa (DOT) 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,

Since you seem to be the genius at joins, do you think this query can
be shortened?

SELECT c.email || '|' || ca.first_name || '|' || ca.last_name ||
'|' || 'ZACKS' || '|' ||
TO_CHAR(c.date_registered, 'mmddrr hh24:mi:ss') || '|' ||
TO_CHAR(TRUNC(SYSDATE-1),'MM-DD-YYYY') || '|' ||
v.adid || '|' || cd.day_phone line, 'HOUSE' list_type
FROM customer c, customer_account ca, visitor v,
subscr_email_product s, customer_address cd
WHERE c.date_registered BETWEEN p_start_date AND p_end_date
AND c.customer_id = ca.customer_id(+)
AND c.customer_id = s.customer_id
AND c.customer_id = v.customer_id
AND c.customer_id = cd.customer_id(+)
AND s.email_product_id = 'HL'
AND email NOT IN (
SELECT email
FROM customer c, customer.subscriptions s, customer.product p,
customer_address ca, customer_account ct, visitor v
WHERE ca.address_type_id = 1
AND s.status = 1
AND s.sell_rep_id IN (201, 202)
AND p.produst_id = 1
AND TRUNC(start_date) BETWEEN p_start_date AND p_end_date
AND p.produst_id = s.produst_id
AND c.customer_id = s.customer_id
AND c.customer_id = ca.customer_id
AND c.customer_id = ct.customer_id
AND c.customer_id = v.customer_id (+))
UNION
SELECT c.email || '|' || ca.first_name || '|' || ca.last_name ||
'|' || 'ZACKS' || '|' ||
TO_CHAR(c.date_registered, 'mmddrr hh24:mi:ss') || '|' ||
TO_CHAR(TRUNC(SYSDATE-1),'MM-DD-YYYY') || '|' ||
v.adid || '|' || cd.day_phone line, 'AMERITRADE' list_type
FROM customer c, customer_account ca, visitor v,
subscr_email_product s, customer_address cd
WHERE c.date_registered BETWEEN p_start_date AND p_end_date
AND c.customer_id = ca.customer_id(+)
AND c.customer_id = s.customer_id
AND c.customer_id = v.customer_id
AND c.customer_id = cd.customer_id(+)
AND s.email_product_id = 'HL'
AND email IN (
SELECT email
FROM customer c, customer.subscriptions s, customer.product p,
customer_address ca, customer_account ct, visitor v
WHERE ca.address_type_id = 1
AND s.status = 1
AND s.sell_rep_id IN (201, 202)
AND p.produst_id = 1
AND TRUNC(start_date) BETWEEN p_start_date AND p_end_date
AND p.produst_id = s.produst_id
AND c.customer_id = s.customer_id
AND c.customer_id = ca.customer_id
AND c.customer_id = ct.customer_id
AND c.customer_id = v.customer_id (+));

#16

On Apr 9, 11:04 am, patrick <pgov...@u.washington.edu> wrote:

Quote:

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

On Apr 9, 10:28 am, Mtek <m... (AT) mtekusa (DOT) 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,

Since you seem to be the genius at joins, do you think this query can
be shortened?

SELECT c.email || '|' || ca.first_name || '|' || ca.last_name ||
'|' || 'ZACKS' || '|' ||
TO_CHAR(c.date_registered, 'mmddrr hh24:mi:ss') || '|' ||
TO_CHAR(TRUNC(SYSDATE-1),'MM-DD-YYYY') || '|' ||
v.adid || '|' || cd.day_phone line, 'HOUSE' list_type
FROM customer c, customer_account ca, visitor v,
subscr_email_product s, customer_address cd
WHERE c.date_registered BETWEEN p_start_date AND p_end_date
AND c.customer_id = ca.customer_id(+)
AND c.customer_id = s.customer_id
AND c.customer_id = v.customer_id
AND c.customer_id = cd.customer_id(+)
AND s.email_product_id = 'HL'
AND email NOT IN (
SELECT email
FROM customer c, customer.subscriptions s, customer.product p,
customer_address ca, customer_account ct, visitor v
WHERE ca.address_type_id = 1
AND s.status = 1
AND s.sell_rep_id IN (201, 202)
AND p.produst_id = 1
AND TRUNC(start_date) BETWEEN p_start_date AND p_end_date
AND p.produst_id = s.produst_id
AND c.customer_id = s.customer_id
AND c.customer_id = ca.customer_id
AND c.customer_id = ct.customer_id
AND c.customer_id = v.customer_id (+))
UNION
SELECT c.email || '|' || ca.first_name || '|' || ca.last_name ||
'|' || 'ZACKS' || '|' ||
TO_CHAR(c.date_registered, 'mmddrr hh24:mi:ss') || '|' ||
TO_CHAR(TRUNC(SYSDATE-1),'MM-DD-YYYY') || '|' ||
v.adid || '|' || cd.day_phone line, 'AMERITRADE' list_type
FROM customer c, customer_account ca, visitor v,
subscr_email_product s, customer_address cd
WHERE c.date_registered BETWEEN p_start_date AND p_end_date
AND c.customer_id = ca.customer_id(+)
AND c.customer_id = s.customer_id
AND c.customer_id = v.customer_id
AND c.customer_id = cd.customer_id(+)
AND s.email_product_id = 'HL'
AND email IN (
SELECT email
FROM customer c, customer.subscriptions s, customer.product p,
customer_address ca, customer_account ct, visitor v
WHERE ca.address_type_id = 1
AND s.status = 1
AND s.sell_rep_id IN (201, 202)
AND p.produst_id = 1
AND TRUNC(start_date) BETWEEN p_start_date AND p_end_date
AND p.produst_id = s.produst_id
AND c.customer_id = s.customer_id
AND c.customer_id = ca.customer_id
AND c.customer_id = ct.customer_id
AND c.customer_id = v.customer_id (+));

#17

On Apr 9, 11:04 am, patrick <pgov...@u.washington.edu> wrote:

Quote:

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

On Apr 9, 10:28 am, Mtek <m... (AT) mtekusa (DOT) 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,

Since you seem to be the genius at joins, do you think this query can
be shortened?

SELECT c.email || '|' || ca.first_name || '|' || ca.last_name ||
'|' || 'ZACKS' || '|' ||
TO_CHAR(c.date_registered, 'mmddrr hh24:mi:ss') || '|' ||
TO_CHAR(TRUNC(SYSDATE-1),'MM-DD-YYYY') || '|' ||
v.adid || '|' || cd.day_phone line, 'HOUSE' list_type
FROM customer c, customer_account ca, visitor v,
subscr_email_product s, customer_address cd
WHERE c.date_registered BETWEEN p_start_date AND p_end_date
AND c.customer_id = ca.customer_id(+)
AND c.customer_id = s.customer_id
AND c.customer_id = v.customer_id
AND c.customer_id = cd.customer_id(+)
AND s.email_product_id = 'HL'
AND email NOT IN (
SELECT email
FROM customer c, customer.subscriptions s, customer.product p,
customer_address ca, customer_account ct, visitor v
WHERE ca.address_type_id = 1
AND s.status = 1
AND s.sell_rep_id IN (201, 202)
AND p.produst_id = 1
AND TRUNC(start_date) BETWEEN p_start_date AND p_end_date
AND p.produst_id = s.produst_id
AND c.customer_id = s.customer_id
AND c.customer_id = ca.customer_id
AND c.customer_id = ct.customer_id
AND c.customer_id = v.customer_id (+))
UNION
SELECT c.email || '|' || ca.first_name || '|' || ca.last_name ||
'|' || 'ZACKS' || '|' ||
TO_CHAR(c.date_registered, 'mmddrr hh24:mi:ss') || '|' ||
TO_CHAR(TRUNC(SYSDATE-1),'MM-DD-YYYY') || '|' ||
v.adid || '|' || cd.day_phone line, 'AMERITRADE' list_type
FROM customer c, customer_account ca, visitor v,
subscr_email_product s, customer_address cd
WHERE c.date_registered BETWEEN p_start_date AND p_end_date
AND c.customer_id = ca.customer_id(+)
AND c.customer_id = s.customer_id
AND c.customer_id = v.customer_id
AND c.customer_id = cd.customer_id(+)
AND s.email_product_id = 'HL'
AND email IN (
SELECT email
FROM customer c, customer.subscriptions s, customer.product p,
customer_address ca, customer_account ct, visitor v
WHERE ca.address_type_id = 1
AND s.status = 1
AND s.sell_rep_id IN (201, 202)
AND p.produst_id = 1
AND TRUNC(start_date) BETWEEN p_start_date AND p_end_date
AND p.produst_id = s.produst_id
AND c.customer_id = s.customer_id
AND c.customer_id = ca.customer_id
AND c.customer_id = ct.customer_id
AND c.customer_id = v.customer_id (+));

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Anyone good with Joins?

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Anyone good with Joins? - 04-09-2008 , 11:14 AM

Re: Anyone good with Joins? - 04-09-2008 , 11:14 AM

Re: Anyone good with Joins? - 04-09-2008 , 11:14 AM

Re: Anyone good with Joins? - 04-09-2008 , 11:23 AM

Re: Anyone good with Joins? - 04-09-2008 , 11:23 AM

Re: Anyone good with Joins? - 04-09-2008 , 11:23 AM

Re: Anyone good with Joins? - 04-09-2008 , 11:23 AM