Re: Query optimisation

How about this:

SELECT locations.*
FROM locations
Inner Join
  (Select distinct Data.[Location Key Id]     From Data
    Where Data.[Customer Key Id]= _at_G_Customer_Key_Id   ) distinctids
ON locations.[Location Key Id]=distinctids.[Location Key Id]

Then for an update to work you would need to set the unique table to be locations.

Another method:
SELECT *
FROM locations
WHERE exists
(SELECT *
FROM Data
WHERE Data.[Customer Key Id]= " & G_Customer_Key_Id & " And Data.[Location Key Id]=locations.[Location Key Id])

"John Smith" <10001_john_smith_at_hotmail.com> wrote in message news:<bgra5o$s1n62$1_at_ID-126646.news.uni-berlin.de>...

> "programmer" <int.consultNOCAPITALS_at_macmail.com> wrote in message

> 
> 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 :-)