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

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.

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
>

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