I am having a problem trying to think of the correct SQL statement I need. It's late in the day and my brain is completely fried.
What I am tring to do is get the average price of one column based a an ID that is passed to the SQL query. For example if a BrandID of 1 is passed into the query it should output an average price of $4.00 The problem arises when one of the BrandID(BNID) columns doesn't contain any of the #ID# numbers in it. The value that it returns is NULL which causes the rest of the SQL statement to return NULL as a result even if the other columns contain at least one #ID# in the BNID column. For example passing a BrandID of 24 will return a resultset of NULL because it is not in the second or third BRANDID column. Hopefully I made some sense. Below is the partial table design and SQL statements I am using in my script. Any help would be great. TABLE DESIGN BNID1_3 Price1_3 BNID2_3 Price2_3 BNID3_3 Price3_3 1 $2.00 10 $0.00 1 $5.00 24 $0.00 1 $5.00 23 $0.00 SQL STATEMENT select (select avg(Price1_3) from temp1 where BNID1_3=#ID#)+ (select avg(Price2_3) from temp1 where BNID2_3=#ID#)+ (select avg(Price3_3) from temp1 where BNID3_3=#ID#) AS AVGPRICE ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
