Re: Select Statement

["Baron Schwartz" <baron@xxxxxxxxx>]

On Mon, Mar 10, 2008 at 1:38 PM, Peter Brawley
<peter.brawley@xxxxxxxxxxxxx> 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/mysql?unsub=royale@xxxxxxxxxx