On Mon, Mar 10, 2008 at 1:38 PM, Peter Brawley <[EMAIL PROTECTED]> wrote: > Velen, > > >My problem is that it is displaying a.sale_id but different customer_name > >as it is taking sale_id from d and matching cust_code with b > > Any non-aggregate SELECTed value that does not have a 1:1 relationship > with your GROUP BY column will show arbitrary results, so the first > thing to get clear on is what the GROUP BY clause is intended to do.
I agree with Peter. To help avoid problems, try this: SET @@sql_mode := 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; Now run your query again. You should get an error if you're selecting a non-grouped column in a GROUP BY query. I think the above settings are sort of a baseline for sanity's sake. They keep you from doing invalid or stupid things without knowing it. MySQL lets you do these things by default. Baron > > PB > > ----- > > Velen wrote: > > In fact my sql statement is like this: > > > > "select b.customer_name Customer,a.sale_id DocNo,a.sale_date > Date,a.prod_code Product,a.quantity Quantity,c.cost_price Cost,a.price Price, > c.prod_description,a.store,d.payMode from sale_trans a,customer_master > b,prod_master c,saletrans_cons d where a.sale_id=d.sale_id and d.cust_code = > b.customer_code And a.prod_code = c.ProdBarcode And a.prod_code between > 'txtbarcodefm' and 'txtbarcodeto' and a.sale_date between 'dtFrom' and 'dtTo' > and a.sbranchid between 'brNmfm' and 'brNmto' group by a.nuniqid order by > .prod_code,a.sale_id,b.customer_name" > > > > The sale_id can be duplicate as different sbranchid can have same sale_id. > My problem is that it is displaying a.sale_id but different customer_name as > it is taking sale_id from d and matching cust_code with b > > > > The tables contains links as follows: > > a contains sale_id > > b contains cust_code > > c contains prodbarcode > > d contains sale_id,cust_code > > > > Can you suggest any correction? > > > > Thanks > > > > > > Velen > > > > ------------------------------------------------------------------------ > > > > No virus found in this incoming message. > > Checked by AVG. > > Version: 7.5.518 / Virus Database: 269.21.7/1323 - Release Date: 3/10/2008 > 11:07 AM > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
