sql 2000 query help  
Author Message
og





PostPosted: Mon Jul 17 16:45:16 CDT 2006 Top

SQL Server Developer >> sql 2000 query help

Hi,

I would like to fetch data from multiple tables along with a max and count.
query looks like

select p.productcode from product p,
productcategoryregion pfa, category bc, order pe, program pn
where p.productcode=pfa.productcode and bc.catid=p.catid and
pn.program_id=pe.program_id
and pe.program_id in (125,126)
group by p.productcode

The above query will display all the productcode from productcategoryregion
table for the program_id (125,126) from order table.

I would like to display
1. last purchasedate (pe.purchasedt) in the above result. Some of the
productcode from pfa table wont exists in order table pe.
2. count of entries in order table (pe) for each productcode and
program_id. Should display '0' if there is no entry in the order table.

I have tried with a sub-query in the select statement. but it showed me
incorrect data. Can someone pls help?


Thanks
Gane

SQL Server177  
 
 
Arnie





PostPosted: Mon Jul 17 16:45:16 CDT 2006 Top

SQL Server Developer >> sql 2000 query help It would help alot if you could include table DDL, sample data in the form
of INSERT statements, and an illustration of the desired results. Without
that, we are just playing guessing games.

This guess may point you in a direction that works.

--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."





> Hi,
>
> I would like to fetch data from multiple tables along with a max and
> count.
> query looks like
>
> select p.productcode from product p,
> productcategoryregion pfa, category bc, order pe, program pn
> where p.productcode=pfa.productcode and bc.catid=p.catid and
> pn.program_id=pe.program_id
> and pe.program_id in (125,126)
> group by p.productcode
>
> The above query will display all the productcode from
> productcategoryregion table for the program_id (125,126) from order table.
>
> I would like to display
> 1. last purchasedate (pe.purchasedt) in the above result. Some of
> the productcode from pfa table wont exists in order table pe.
> 2. count of entries in order table (pe) for each productcode and
> program_id. Should display '0' if there is no entry in the order table.
>
> I have tried with a sub-query in the select statement. but it showed me
> incorrect data. Can someone pls help?
>
>
> Thanks
> Gane
>
>
>
>
>


 
 
Arnie





PostPosted: Mon Jul 17 17:02:28 CDT 2006 Top

SQL Server Developer >> sql 2000 query help This is a multi-part message in MIME format.

------=_NextPart_000_1263_01C6A9B2.0580D580
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Oops, forgot to inclose this.

SELECT
p.ProductCode
, LastPurchaseDate =3D ( SELECT max( purchasedt )=20
FROM Order=20
WHERE ProductCode =3D p.ProductCode=20
)
, NumberOrders =3D ( SELECT isnull( count(1), 0 )=20
FROM Order=20
WHERE ( ProductCode =3D p.ProductCode=20
AND ProgramID =3D pn.ProgramID
)
)
FROM Product p
JOIN ProductCategoryRegion pfa
ON p.ProductCode=3Dpfa.ProductCode
JOIN Category bc
ON bc.CatID=3Dp.CatID
JOIN Order pe
ON pe.ProductCode =3D pfa.ProductCode
JOIN Program pn
ON pn.Program_id=3Dpe.Program_id
WHERE pe.Program_id in ( 125, 126 )
GROUP BY p.ProductCode

--=20
Arnie Rowland
"To be successful, your heart must accompany your knowledge."





> It would help alot if you could include table DDL, sample data in the =
form=20
> of INSERT statements, and an illustration of the desired results. =
Without=20
> that, we are just playing guessing games.
>=20
> This guess may point you in a direction that works.
>=20
> --=20
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>=20
>=20
>=20


>> Hi,
>>
>> I would like to fetch data from multiple tables along with a max and=20
>> count.
>> query looks like
>>
>> select p.productcode from product p,
>> productcategoryregion pfa, category bc, order pe, program pn
>> where p.productcode=3Dpfa.productcode and bc.catid=3Dp.catid and=20
>> pn.program_id=3Dpe.program_id
>> and pe.program_id in (125,126)
>> group by p.productcode
>>
>> The above query will display all the productcode from=20
>> productcategoryregion table for the program_id (125,126) from order =
table.
>>
>> I would like to display
>> 1. last purchasedate (pe.purchasedt) in the above result. Some =
of=20
>> the productcode from pfa table wont exists in order table pe.
>> 2. count of entries in order table (pe) for each productcode and =

>> program_id. Should display '0' if there is no entry in the order =
table.
>>
>> I have tried with a sub-query in the select statement. but it showed =
me=20
>> incorrect data. Can someone pls help?
>>
>>
>> Thanks
>> Gane
>>
>>
>>
>>
>>=20
>=20
>
------=_NextPart_000_1263_01C6A9B2.0580D580
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.5296.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2>Oops, forgot to inclose =
this.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" =
size=3D2>SELECT<BR>&nbsp;&nbsp;&nbsp;&nbsp;=20
p.ProductCode<BR>&nbsp;&nbsp; , LastPurchaseDate =3D ( SELECT max( =
purchasedt )=20
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;=20
FROM Order=20
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;=20
WHERE ProductCode =3D p.ProductCode=20
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
)<BR>&nbsp;&nbsp; , NumberOrders&nbsp;&nbsp;&nbsp;&nbsp; =3D ( SELECT =
isnull(=20
count(1), 0 )=20
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;=20
FROM Order=20
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;=20
WHERE (&nbsp;&nbsp; ProductCode =3D p.ProductCode=20
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
AND ProgramID&nbsp;&nbsp; =3D=20
pn.ProgramID<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
)<BR>FROM Product p<BR>&nbsp;&nbsp; JOIN ProductCategoryRegion=20
pfa<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON=20
p.ProductCode=3Dpfa.ProductCode<BR>&nbsp;&nbsp; JOIN Category=20
bc<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON =
bc.CatID=3Dp.CatID<BR>&nbsp;&nbsp; JOIN=20
Order pe<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON pe.ProductCode =3D=20
pfa.ProductCode<BR>&nbsp;&nbsp; JOIN Program=20
pn<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON =
pn.Program_id=3Dpe.Program_id<BR>WHERE=20
pe.Program_id in ( 125, 126 )<BR>GROUP BY p.ProductCode</FONT></DIV>
<DIV><BR><FONT face=3DArial size=3D2>-- <BR>Arnie Rowland<BR>"To be =
successful, your=20
heart must accompany your knowledge."</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial><BR><FONT size=3D2></FONT></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>"Arnie Rowland" &lt;</FONT><A=20


in message=20

face=3DArial=20

face=3DArial=20
size=3D2>...</FONT></DIV><FONT face=3DArial size=3D2>&gt; It would help =
alot if you=20
could include table DDL, sample data in the form <BR>&gt; of INSERT =
statements,=20
and an illustration of the desired results. Without <BR>&gt; that, we =
are just=20
playing guessing games.<BR>&gt; <BR>&gt; This guess may point you in a =
direction=20
that works.<BR>&gt; <BR>&gt; -- <BR>&gt; Arnie Rowland<BR>&gt; "To be=20
successful, your heart must accompany your knowledge."<BR>&gt; <BR>&gt; =
<BR>&gt;=20

face=3DArial=20

in message=20
<BR>&gt; </FONT><A =

face=3DArial =

face=3DArial size=3D2>...<BR>&gt;&gt; Hi,<BR>&gt;&gt;<BR>&gt;&gt; I =
would like to=20
fetch data from multiple tables along with a max and <BR>&gt;&gt;=20
count.<BR>&gt;&gt; query looks like<BR>&gt;&gt;<BR>&gt;&gt; select =
p.productcode=20
from product p,<BR>&gt;&gt; productcategoryregion pfa, category bc, =
order pe,=20
program pn<BR>&gt;&gt; where p.productcode=3Dpfa.productcode and =
bc.catid=3Dp.catid=20
and <BR>&gt;&gt; pn.program_id=3Dpe.program_id<BR>&gt;&gt; and =
pe.program_id in=20
(125,126)<BR>&gt;&gt; group by p.productcode<BR>&gt;&gt;<BR>&gt;&gt; The =
above=20
query will display all the productcode from <BR>&gt;&gt; =
productcategoryregion=20
table for the program_id (125,126) from order =
table.<BR>&gt;&gt;<BR>&gt;&gt; I=20
would like to display<BR>&gt;&gt;&nbsp;&nbsp;&nbsp; 1.&nbsp;&nbsp;&nbsp; =
last=20
purchasedate (pe.purchasedt) in the above result. Some of <BR>&gt;&gt; =
the=20
productcode from pfa table wont exists in order table=20
pe.<BR>&gt;&gt;&nbsp;&nbsp;&nbsp; 2.&nbsp;&nbsp; count of entries in =
order table=20
(pe) for each productcode and <BR>&gt;&gt; program_id. Should display =
'0' if=20
there is no entry in the order table.<BR>&gt;&gt;<BR>&gt;&gt; I have =
tried with=20
a sub-query in the select statement. but it showed me <BR>&gt;&gt; =
incorrect=20
data. Can someone pls help?<BR>&gt;&gt;<BR>&gt;&gt;<BR>&gt;&gt;=20
Thanks<BR>&gt;&gt;=20
Gane<BR>&gt;&gt;<BR>&gt;&gt;<BR>&gt;&gt;<BR>&gt;&gt;<BR>&gt;&gt; =
<BR>&gt;=20
<BR>&gt;</FONT></BODY></HTML>

------=_NextPart_000_1263_01C6A9B2.0580D580--