With GROUP BY, each item in a SELECT List is either part of the GROUP BY clause or an aggregate function/expression - you can't have anything in limbo...
I assume you are NOT using SELECT *...! If not - do you really need to return all those columns... and how many? In order to resolve a Cartesian product, try to build your query in steps. Start with two tables and make sure you don't get any duplicates returned, and add one table at a time. Think of it as joining the result of the previous query with data from the added table ((A JOIN B ON <compare A and B>) JOIN C ON <compare AB and C)...) If the query was developed by someone else and you don't quite understand it because it is too complex and you are not overly familiar with the database, it can be helpful to create a test with tables that have different number of rows - each a different number and each number of rows being a prime number <> 1. So table A would have 2 rows, table B would have 3 rows, table C would have 5 rows, etc. Construct your data sets so that a proper result set would have exactly ONE result row in an INNER join between the tables. Run your query, and count the records returned (no DISTINCT, no GROUP BY). Determine the factors this number is divisible by, and you know which table(s) contribute multiple rows and therefore aren't properly matched to the other tables. That should point you in the right direction. HTH, Tore. -----Original Message----- From: Sam Thompson [mailto:[EMAIL PROTECTED]] Sent: Friday, September 27, 2002 9:55 AM To: ActiveServerPages Subject: Re: GROUP BY question Excellent reply, thanks Tore. I think I have got a Cartesian product, but I dont know what to do about it! Also, I get this error when trying to use GROUP BY without using an aggregate function: Column 'tblJob.JobRef' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause ...so I went and included every single column from all the tables which I have inner joinned, but I exceeded the maximum allowed in the GROUP BY clause. What I am doing wrong here? Thanks, Sam ----- Original Message ----- From: "Bostrup, Tore" <[EMAIL PROTECTED]> To: "ActiveServerPages" <[EMAIL PROTECTED]> Sent: Friday, September 27, 2002 2:29 PM Subject: RE: GROUP BY question > Beware your question... > > GROUP BY without any aggregate functions works like a DISTINCT. If you get > multiple copies of the same data, make sure that is not a result of a > Cartesian product in the underlying query. In this case, although you will > get fewer rows back (it eliminates the duplicate rows), your query is just > as inefficient - except for the network traffic generated by your returned > data. > > HTH, > Tore. > > -----Original Message----- > From: Sam Thompson [mailto:[EMAIL PROTECTED]] > Sent: Friday, September 27, 2002 4:12 AM > To: ActiveServerPages > Subject: GROUP BY question > > > Will using GROUP BY help me cut down the amount of records that are > returned? Or does it just organise them? > > Can I use it without using an aggregate function? > > Thanks, Sam > > > --- > You are currently subscribed to activeserverpages as: [EMAIL PROTECTED] > To unsubscribe send a blank email to > %%email.unsub%% > > --- > You are currently subscribed to activeserverpages as: [EMAIL PROTECTED] > To unsubscribe send a blank email to %%email.unsub%% > --- You are currently subscribed to activeserverpages as: [EMAIL PROTECTED] To unsubscribe send a blank email to %%email.unsub%% --- You are currently subscribed to activeserverpages as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED]
