Sure can you reference 4 tables in your from clause and in your where clause
you are only joining 2 of the tables :
> u.user_id *= dn.user_id
So all the tables that you didn't restrict in your join are build you a
cartesian join. To avoid this kind of error use the ANSI JOIN sytax. I
tried to write an example for you below but I didn't have all the
information to really fill it out, but you should get the idea.
-eric
SELECT
u.first_nm,
u.last_nm,
c.category_nm,
c.category_id,
c.dataType,
c.department_id,
dn.date_dt,
SUM(dn.quantity_nb)AS totalForMonth,
g.goal_nb
FROM
tblUsers u,
INNER JOIN
tblDailyNumbers dn ON u.user_id = dn.user_id
INNER JOIN
tblGoals g ON g.____ = __._______
INNER JOIN
tblcategories c ON c.____ = ___._____
WHERE
DatePart("mm", dn.date_dt) = 9 AND
DatePart("yy", dn.date_dt) = 2001
GROUP BY u.last_nm,
c.category_nm,
c.category_id,
c.department_id,
g.goal_nb,
c.dataType,
ufirst_nm,
dn.date_dt
----- Original Message -----
From: "John Barleycorn" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Sunday, September 30, 2001 5:40 PM
Subject: Unexpected query results
> Hello everyone, i'm currently running a query that is returning some odd
> results. I'm trying to get the total number of sales in each category for
a
> salesperson. The query i'm running is below. With the data i have right
now
> i would expect to get back 4 for the total of sales per category for the
> month indicated. I am however, getting back a total of 144. Can anyone
tell
> me what it is that is incorrect in my SQL statement that would cause this?
> Thank you for your help.
>
> j
>
> SELECT
> u.first_nm
> , u.last_nm
> , c.category_nm
> , c.category_id
> , c.dataType
> , c.department_id
> , dn.date_dt
> , SUM(dn.quantity_nb)AS totalForMonth
> , g.goal_nb
> FROM
> tblcategories c
> , tblDailyNumbers dn
> , tblUsers u
> , tblGoals g
> WHERE
> DatePart("mm", dn.date_dt) = 9
> AND
> DatePart("yy", dn.date_dt) = 2001
> AND
> u.user_id *= dn.user_id
> GROUP BY u.last_nm
> , c.category_nm
> , c.category_id
> , c.department_id
> , g.goal_nb
> , c.dataType
> , u.first_nm
> , dn.date_dt
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
