On Apr 9, 8:42=A0am, Mtek <m...@[EMAIL PROTECTED]
> wrote:
> 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. =A0However, 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? =A0If I find a good example searching the net it'd be
> > great. =A0but 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,
> > =A0 =A0 =A0 =A0p.price, co.customer_id, ol.status, co.confirm, NULL
adid=
> > FROM customer_order co, order_line ol, product p
> > WHERE co.order_id =3D ol.order_id AND ol.product_id =3D p.product_id
> > =A0 AND co.date_entered >=3D TRUNC(SYSDATE) - 7 AND p.subproduct_id IN
> > =A0 (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,
> > =A0 =A0 =A0 =A0p.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 =3D ol.order_id AND ol.product_id =3D p.product_id
> > =A0 AND ol.order_id =3D ss.order_id AND co.date_entered >=3D
TRUNC(SYSDA=
TE)
> > - 7 AND p.subproduct_id IN
> > =A0 =A0 (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,
> =A0 =A0 =A0 =A0p.price, co.customer_id, ol.status, co.confirm, ss.adid,
> ol.order_id, co.customer_id
> FROM engine.customer_order co
> =A0 JOIN engine.order_line ol ON (co.order_id =3D ol.order_id)
> =A0 JOIN engine.product p ON (ol.product_id =3D p.product_id)
> =A0 JOIN data_holder.shopcart_sessions ss ON (ol.order_id =3D
ss.order_id)=
> WHERE co.date_entered >=3D TRUNC(SYSDATE) - 7 AND p.subproduct_id IN
> =A0 =A0 (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 =3D ol.order_id
AND ol.product_id =3D p.product_id
AND co.date_entered >=3D TRUNC(SYSDATE)- 7
AND p.subproduct_id IN (SELECT newsletter_id FROM product_group)
and ss.order_id(+) =3D ol.order_id
ORDER BY date_entered DESC;
=3D=3D=3D=3D>Patrick
|
