If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query Help
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 |
#2
|
|||
|
|||
Query Help
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" wrote in message m... 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 |
#3
|
|||
|
|||
Query Help
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. "Jeff Boyce" wrote in message ... 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" wrote in message m... 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 |
#4
|
|||
|
|||
Query Help
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 booner wrote: 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. "Jeff Boyce" wrote in message ... 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" wrote in message m... 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 |
#5
|
|||
|
|||
Query Help
Excellent point!
"John Spencer" wrote in message ... 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 booner wrote: 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. "Jeff Boyce" wrote in message ... 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" wrote in message m... 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 |
Thread Tools | |
Display Modes | |
|
|