Quick SQL question that I should know the answer to

Factual Questions
1

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:

Objects: objectPrimaryKey (integer), objectName (string), groupForeignKey (integer)
Groups: groupPrimaryKey (integer), groupName (string)

I’ve tried to set the query up, but I can’t figure out a way to do it without subqueries and inner joins and all that, and it gets me badly confused. What’s the simplest way to do this one?

2

SELECT objectName FROM objects WHERE groupForeignKey = (SELECT groupForeignKey FROM objects WHERE objectName = ‘something’)

It involves a subquery (I think, it’s been a while since I did SQL), but what’s wrong with that?

3

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)

So

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.

4

I see Absolute beat me to it.

5

Hmm. I can’t see what the problem with joins and subqueries is either - my query is the same as Absolute and Noone Special, except that this one will work if multiple groups are matched on.

select “OName” from “Objects”
where gFK in
(select gPK from “Groups” where GName = @SomeParameter)

6

There’s nothing wrong with using a subquery. There is something wrong with the way I was trying to do it, though–not quite so simple. Thanks.

7

That’s always my problem too. :wink:

If you prefer a join, it would go something like:

select “OName” from
“Objects” o
left join
“Groups” g on o.gFK = g.gPK
where GName = @SomeParameter