I can't get customized pricing to work

Hi,
I don't know much about SQL. So, be gentle with me. . . I have a product table with 350 records. I also have some customers who need customized prices for some of the products in that table.

I created a customer price table that includes the customer ID, product ID and custom pricing. I hoped to write a SP that would display all 350 products, and custom pricing, if any, for the customer that is logged in.

Here is my attempt:
The Product Table is DT_Products. The custom pricing table is DT_CustomerPrices. In this example the customer ID is 4415
<code>
SELECT DT_Products.itemcode, DT_Products.price, DT_CustomerPrices.custitemcode, DT_CustomerPrices.custPrice, DT_CustomerPrices.Custnum, DT_Products.active
FROM DT_Products LEFT JOIN DT_CustomerPrices ON DT_Products.itemcode = DT_CustomerPrices.custitemcode
WHERE (((DT_CustomerPrices.Custnum)='4415') AND ((DT_Products.active)=1)) OR (((DT_CustomerPrices.Custnum) Is Null) AND ((DT_Products.active)=1))</code>

The results display only 212 of the 350 products, if the customer has NO custom prices.
If the customer has any custom prices, the results are increased by the number of custom prices.

Any help getting my query to display all 350 products with custom prices, would be greatly appreciated.
Thanks,
Jim

Do you have table schema and sample data?

create table dbo.DT_CustomerPrices(
SalesRep varchar(3) NOT NULL,
CustID char(10) NOT NULL,
ID numeric() identity(18) NOT NULL,
custitemcode nvarchar(20) NOT NULL,
custPrice varchar(8) NULL,
DateEntered datetime NULL,
Custnum varchar(4) NULL
)

create table dbo.DT_Products(
ID numeric() identity(18) NOT NULL,
itemcode nvarchar(20) NOT NULL,
price varchar(8) NULL,
description ntext NULL,
pic nvarchar(75) NULL,
productsize nvarchar(25) NULL,
taxfree int NULL,
brand nvarchar(25) NULL,
searchcode nvarchar(3) NULL,
title nvarchar(50) NULL,
briefdesc nvarchar(250) NULL

)

This is some sample data from the DT_customerPrices Table

SalesRep CustID ID custitemcode custPrice DateEntered Custnum
TE alldia 97 129G 10.00 4/18/2006 4497
LD ALLDIA 102 26B-6 50 11/13/2006 4455
DB ads 130 ACCO51 24.75 4428
DB ads 131 ACML 9.5 4428
DB ads 132 ACTMC50 24.95 4428
DB ads 133 AVMC50 25.43 4428
DB ads 134 AVMTR 27.5 4428
DB ads 135 CCHO50 24.25 4428
DB ads 136 COCS 4.58 4428
DB ads 137 FLASH 18.9 4428
DB ads 138 FSINMK 10.56 4428
DB ads 139 FTMO50 24 4428
DB ads 140 OTMC50 25.3 4428

Here is some sample data from the DT_Products table:

ID itemcode price description pic productsize taxfree brand searchcode title briefdesc
333 10100N $50.00 3" Walker Wheels images/products/10100N.jpg 1pr/bx dri hom 3" Walker Wheels with Rear Glide Caps, Grey 3" Walker Wheels with Rear Glide Caps, Grey
334 10100NBL 0 3" Walker Wheels images/products/10100N.jpg 1pr/bx dri hom 3" Walker Wheels with Rear Glide Caps, Blue 3" Walker Wheels with Rear Glide Caps, Blue
90 10100NG 0 3" Walker Wheels images/products/10100N.jpg 1pr/bx dri hom 3" Walker Wheels with Rear Glide Caps, Green 3" Walker Wheels with Rear Glide Caps, Green
335 10100NGL 0 3" Walker Wheels images/products/10100N.jpg 1pr/bx dri hom 3" Walker Wheels with Rear Glide Caps, Gold 3" Walker Wheels with Rear Glide Caps, Gold
336 10100NP 0 3" Walker Wheels images/products/10100N.jpg 1pr/bx dri hom 3" Walker Wheels with Rear Glide Caps, Pink 3" Walker Wheels with Rear Glide Caps, Pink
337 10101N 0 3" Walker Wheels images/products/10101N.jpg 1pr/bx dri hom 5" Walker Wheels with Rear Glide Caps 5" Walker Wheels with Rear Glide Caps

SELECT DT_Products.itemcode, DT_Products.price, DT_CustomerPrices.custitemcode, DT_CustomerPrices.custPrice, DT_CustomerPrices.Custnum, DT_Products.active
FROM DT_Products LEFT JOIN DT_CustomerPrices ON DT_Products.itemcode = DT_CustomerPrices.custitemcode
WHERE DT_CustomerPrices.Custnum='4415' AND DT_Products.active=1

I made one small change. I put an AND statement inside the JOIN <code>
AND DT_CustomerPrices.Custnum='4415'
</code> moving it from the WHERE clause.
<ORIGINAL CODE>
SELECT DT_Products.itemcode, DT_Products.price, DT_CustomerPrices.custitemcode, DT_CustomerPrices.custPrice, DT_CustomerPrices.Custnum, DT_Products.active
FROM DT_Products LEFT JOIN DT_CustomerPrices ON DT_Products.itemcode = DT_CustomerPrices.custitemcode
WHERE DT_CustomerPrices.Custnum='4415' AND DT_Products.active=1
</ORIGINAL CODE>


<Adjusted code>
SELECT DT_Products.itemcode, DT_Products.price, DT_CustomerPrices.custitemcode, DT_CustomerPrices.custPrice, DT_CustomerPrices.Custnum, DT_Products.active
FROM DT_Products LEFT JOIN DT_CustomerPrices ON DT_Products.itemcode = DT_CustomerPrices.custitemcode AND DT_CustomerPrices.Custnum='4415'
WHERE DT_Products.active=1
</Adjusted code>
That fixed it! the results display all 350 products plus the custom pricing for the logged in customer.
Thanks!