Re: Query optimisation

"programmer" <int.consultNOCAPITALS_at_macmail.com> wrote in message
>
> > Two tables Locations and Data
> > Locations has PK Location Key Id
> > Data has FK Location Key Id
> >
> > The query
> >
> > SELECT *
> > FROM locations
> > WHERE locations.[location key id]
> > IN
> > (SELECT distinct Data.[Location Key Id]
> > FROM Data
> > WHERE Data.[Customer Key Id]= " & G_Customer_Key_Id & ");"
>
>
> SELECT *
> FROM locations, data
> WHERE Data.[Customer Key Id]= " & G_Customer_Key_Id & ")"
> and locations.[location key id] = Data.[Location Key Id];

Hi, thanks for that, unfortunatly that pulls a record of each location in data, when I really just need the distinct locations that match table Locations.

This seems to do the trick but unfortunatly I can'T update the db with this query

SELECT DISTINCT Locations.[location key id],Locations.[Location Id], Locations.[Location Description] From locations, Data
WHERE Data.[Customer Key Id]= " & G_Customer_Key_Id & " AND locations.[location key id] = Data.[Location Key Id];

I think the distinct clause makes it non-updateable because it's an aggregate function.

Any more ideas :-) Received on Wed Aug 06 2003 - 18:27:41 CEST