# Miscellaneous > Structured Query Language (SQL) >  I can't get customized pricing to work

## ddiabetes

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

----------


## rmiao

Do you have table schema and sample data?

----------


## ddiabetes

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

)

----------


## ddiabetes

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

----------


## willy

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

----------


## ddiabetes

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!

----------

