Re: Query optimisation

From: Jon <junkaddress10_at_hotmail.com>
Date: 7 Aug 2003 06:47:59 -0700
Message-ID: <213b8522.0308070547.664a4219@posting.google.com>

How about this:

SELECT locations.*
FROM locations
Inner Join
(Select distinct Data.[Location Key Id] From Data
Where Data.[Customer Key Id]= @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

> >
> > > 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 Thu Aug 07 2003 - 08:47:59 CDT