Anyone good with Joins? [Oracle Help]

Prev: Charset problem / PHP
Next: Nasty Query Here

From: Mtek on 9 Apr 2008 11:28

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;

From: Mtek on 9 Apr 2008 11:42

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

From: patrick on 9 Apr 2008 12:04

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

From: Mtek on 9 Apr 2008 12:14

From: Mtek on 9 Apr 2008 12:23

On Apr 9, 11:04 am, patrick <pgov...(a)u.washington.edu> wrote:
> On Apr 9, 8:42 am, Mtek <m...(a)mtekusa.com> wrote:
>
>
>
> > On Apr 9, 10:28 am, Mtek <m...(a)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,

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 (+));

|
Pages: 1
Prev: Charset problem / PHP
Next: Nasty Query Here