Bendik August Nesbø Bendik August Nesbø - 5 months ago 12
SQL Question

Retrieve many-to-many ids as array

I have three tables,

Foos
,
Bars
, and a many-to-many table
FoosBars
. Each table have their own auto-incrementing
INT
as primary key, and
FoosBars
has two columns:
FooId
(foreign key to
Foos.Id
) and
BarsId
(foreign key to
Bars.Id
). In my application (C#) I want to populate my
Foo
-objects with the columns from the
Foos
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
Foo
-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

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