Query Help

[booner]

I have 3 tables ... 1 table list of devices ... 2 other tables contain
references to those devices.

Schema Overview:
Devices - Id - AutoNumber
CSDevices - DeviceID - foreign key to Id of Devices
FSDevices - DeviceID - foreign key to Id of Devices

What I want to get is a distinct list of devices used. I created this
query - and for smaller data sets works just fine:

SELECT DISTINCT Devices.* FROM FSDevices,Devices,CSDevices WHERE
Devices.Id=CSDevices.DeviceId OR Devices.Id=FSDevices.DeviceId

However, when there are lots of CSDevices and FSDevices - the query seems to
lock up (runs for a very, very, very long time).

So looking for a better way to generate this list.

Any/all help much appreciated.

BBB

 

[Jeff Boyce]

I may be missing something...

If your first table ([Devices]) holds one record per device, wouldn't that
be the place to get a list of each device?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[booner]

I probably wasn't clear ... devices is reference data - csdevices,
fsdevices - are which devices are actually in use.

I think I have it now:

SELECT DISTINCT Devices.* FROM CSDevices,Devices WHERE
CSDevices.DeviceId=Devices.Id UNION SELECT DISTINCT Devices.* FROM
FSDevices,Devices WHERE FSDevices.DeviceId=Devices.Id

This gets me same data set - but much faster - and works on small and large
data sets.

I may be missing something...

If your first table ([Devices]) holds one record per device, wouldn't that
be the place to get a list of each device?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have 3 tables ... 1 table list of devices ... 2 other tables contain
references to those devices.

Schema Overview:
Devices - Id - AutoNumber
CSDevices - DeviceID - foreign key to Id of Devices
FSDevices - DeviceID - foreign key to Id of Devices

What I want to get is a distinct list of devices used. I created this
query - and for smaller data sets works just fine:

SELECT DISTINCT Devices.* FROM FSDevices,Devices,CSDevices WHERE
Devices.Id=CSDevices.DeviceId OR Devices.Id=FSDevices.DeviceId

However, when there are lots of CSDevices and FSDevices - the query seems
to lock up (runs for a very, very, very long time).

So looking for a better way to generate this list.

Any/all help much appreciated.

BBB

 

[John Spencer]

It might be even faster if you use this variant.

SELECT Devices.*
FROM CSDevices INNER JOIN Devices
ON CSDevices.DeviceId=Devices.Id
UNION
SELECT Devices.*
FROM FSDevices INNER JOIN Devices
ON FSDevices.DeviceId=Devices.Id

Union will strip out duplicates so that only gets done once instead of once in
each query and then again when the UNION executes.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I probably wasn't clear ... devices is reference data - csdevices,
fsdevices - are which devices are actually in use.

I think I have it now:

SELECT DISTINCT Devices.* FROM CSDevices,Devices WHERE
CSDevices.DeviceId=Devices.Id UNION SELECT DISTINCT Devices.* FROM
FSDevices,Devices WHERE FSDevices.DeviceId=Devices.Id

This gets me same data set - but much faster - and works on small and large
data sets.

I may be missing something...

If your first table ([Devices]) holds one record per device, wouldn't that
be the place to get a list of each device?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have 3 tables ... 1 table list of devices ... 2 other tables contain
references to those devices.

Schema Overview:
Devices - Id - AutoNumber
CSDevices - DeviceID - foreign key to Id of Devices
FSDevices - DeviceID - foreign key to Id of Devices

What I want to get is a distinct list of devices used. I created this
query - and for smaller data sets works just fine:

SELECT DISTINCT Devices.* FROM FSDevices,Devices,CSDevices WHERE
Devices.Id=CSDevices.DeviceId OR Devices.Id=FSDevices.DeviceId

However, when there are lots of CSDevices and FSDevices - the query seems
to lock up (runs for a very, very, very long time).

So looking for a better way to generate this list.

Any/all help much appreciated.

BBB

 

[booner]

Excellent point!

It might be even faster if you use this variant.

SELECT Devices.*
FROM CSDevices INNER JOIN Devices
ON CSDevices.DeviceId=Devices.Id
UNION
SELECT Devices.*
FROM FSDevices INNER JOIN Devices
ON FSDevices.DeviceId=Devices.Id

Union will strip out duplicates so that only gets done once instead of
once in each query and then again when the UNION executes.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I probably wasn't clear ... devices is reference data - csdevices,
fsdevices - are which devices are actually in use.

I think I have it now:

SELECT DISTINCT Devices.* FROM CSDevices,Devices WHERE
CSDevices.DeviceId=Devices.Id UNION SELECT DISTINCT Devices.* FROM
FSDevices,Devices WHERE FSDevices.DeviceId=Devices.Id

This gets me same data set - but much faster - and works on small and
large data sets.

I may be missing something...

If your first table ([Devices]) holds one record per device, wouldn't
that be the place to get a list of each device?

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have 3 tables ... 1 table list of devices ... 2 other tables contain
references to those devices.

Schema Overview:
Devices - Id - AutoNumber
CSDevices - DeviceID - foreign key to Id of Devices
FSDevices - DeviceID - foreign key to Id of Devices

What I want to get is a distinct list of devices used. I created this
query - and for smaller data sets works just fine:

SELECT DISTINCT Devices.* FROM FSDevices,Devices,CSDevices WHERE
Devices.Id=CSDevices.DeviceId OR Devices.Id=FSDevices.DeviceId

However, when there are lots of CSDevices and FSDevices - the query
seems to lock up (runs for a very, very, very long time).

So looking for a better way to generate this list.

Any/all help much appreciated.

BBB