# Quick SQL question that I should know the answer to
**URL:** https://boards.straightdope.com/t/quick-sql-question-that-i-should-know-the-answer-to/357547
**Category:** Factual Questions
**Created:** [May 20, 2006, 5:50pm UTC](https://boards.straightdope.com/t/quick-sql-question-that-i-should-know-the-answer-to/357547 "2006-05-20T17:50:21Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)
#### Post date: [May 20, 2006, 5:50pm UTC](https://boards.straightdope.com/t/quick-sql-question-that-i-should-know-the-answer-to/357547/1 "2006-05-20T17:50:21Z")
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?
---
### Author:  [@Absolute](https://boards.straightdope.com/u/Absolute)
#### Post date: [May 20, 2006, 6:05pm UTC](https://boards.straightdope.com/t/quick-sql-question-that-i-should-know-the-answer-to/357547/2 "2006-05-20T18:05:49Z")
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?
---
### Author:  [@Noone\_Special](https://boards.straightdope.com/u/Noone_Special)
#### Post date: [May 20, 2006, 6:08pm UTC](https://boards.straightdope.com/t/quick-sql-question-that-i-should-know-the-answer-to/357547/3 "2006-05-20T18:08:35Z")
> [@ultrafilter](#):
>
> 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?
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.
---
### Author:  [@Noone\_Special](https://boards.straightdope.com/u/Noone_Special)
#### Post date: [May 20, 2006, 6:09pm UTC](https://boards.straightdope.com/t/quick-sql-question-that-i-should-know-the-answer-to/357547/4 "2006-05-20T18:09:39Z")
I see **Absolute** beat me to it.
---
### Author:  [@Nanoda](https://boards.straightdope.com/u/Nanoda)
#### Post date: [May 20, 2006, 6:13pm UTC](https://boards.straightdope.com/t/quick-sql-question-that-i-should-know-the-answer-to/357547/5 "2006-05-20T18:13:10Z")
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)
---
### Author:  [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)
#### Post date: [May 20, 2006, 6:13pm UTC](https://boards.straightdope.com/t/quick-sql-question-that-i-should-know-the-answer-to/357547/6 "2006-05-20T18:13:13Z")
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.
---
### Author:  [@Nanoda](https://boards.straightdope.com/u/Nanoda)
#### Post date: [May 20, 2006, 6:19pm UTC](https://boards.straightdope.com/t/quick-sql-question-that-i-should-know-the-answer-to/357547/7 "2006-05-20T18:19:29Z")
That’s always my problem too. 😉
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