On Mon, Mar 10, 2008 at 1:38 PM, Peter Brawley
<peter.brawley@stripped> 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
> >
>
