Subject Re: How to create index for field in two tables and had any limitation
Author Adam
--- In, Helen Borrie <helebor@...>
> At 02:35 PM 21/06/2006, Adam wrote:
> >Select * From SalesOrder
> >Join Customer on C.CustomerCode = S.CustomerCode
> >order by customername
> >
> >That gives the optimiser more choice.
> I guess Adam meant:
> Select S.* From SalesOrder S
> Join Customer C on C.CustomerCode = S.CustomerCode
> order by C.customername
> ./heLen

Adam suspects that is mostly what was meant, but we do not have
enough information to make that assumption safely. All I did was to
copy-paste and removed the word left.

I have seen extremely poor normalisaton in the past where a name was
duplicated to the detail record because the person desiging it wasn't
familiar with joins. Also, SalesOrder could be a view which contains
that field.

Note to Jeremy:

1) Never use select *, specifically name each field you want. By
using select *, the first person to add a new field to customer will
probably break something.

2) Never mention a field name without qualifying which table or alias
it refers to where more than one table is involved. If the same field
appears in multiple tables now or some time in the future, you will
break something.

I suspect Jeremy is also interested in some information from
Customer, there is usually little point ordering something without
selecting the field you are ordering by.