I’ve got an MS Access database tracking a family of objects that are divided into groups based on similarity. What I’d like to do is set up a query that takes the name of an object and returns every object in the same group. Here are the relevant portions of my table structure:
My SQL is a bit rusty, but on the face of it I don’t see how you can avoid a subquery – you want to return all objects belonging to a group (query), which is determined by an ObjectName (inner query)
SELECT * FROM Objects WHERE groupForeignKey = (SELECT groupForeignKey FROM Objects WHERE objectName = ‘?’)
(Where ? is the objectName you want to match, of course)
Don’t see how it can be done more simply.
Caveat – if objectName does not have a Unique Constraint set, you could end up retrieving the whole table, possibly multiple times (although you can avoid this by adding a DISTINCT on either query, I suppose…)! I’m assuming the objectName only appears once, otherwise the whole question is essentially meaningless.