Bendik August Nesbø Bendik August Nesbø - 1 year ago 55
SQL Question

Retrieve many-to-many ids as array

I have three tables,

, and a many-to-many table
. Each table have their own auto-incrementing
as primary key, and
has two columns:
(foreign key to
) and
(foreign key to
). In my application (C#) I want to populate my
-objects with the columns from the
table, plus an array/list of associated BarIds. For now, i have queried the database twice: once to retrieve all the Foos I want(
select * from Foos where X
), and then again to retrieve the FoosBars (
select * from FoosBars inner join Foos on Foos.Id=FoosId where X
), and then populate each
-object's BarIds in my application.

This however requires me to store the FoosBars objects temporarily in my application, and I need to have a class to represent the FoosBars, which I would like to avoid. Is there a way to retrieve the correct FoosBars as an array?

I had something like this in mind:

Select *, (select BarsId
from [this Foo]
inner join FoosBars on Foos.Id=FoosId
) as BarIds
From Foos
Where X

Answer Source

Subquery in the select must return one value so the provided example is invalid. Moreover when you execute a query you should expect a result set (i.e collection of rows), not an array, or any other type.

Nothing prevents you from using it in the most simple manner:

select *
from [Foos]
inner join FoosBars on Foos.Id=FoosId

You might need to consider changing the way you populate your objects based on this result set.

For each row:

  • If you encounter new Foo -> Create it, otherwise ignore it
  • Add a FoosBars item to an existing Foo