SQL dynamic in query

Question

Suppose we have two tables Foo and Bar. I have an association table Foo_Bar for a many-to-many relationship between Foos and Bars.

Now I basically want a query to select the Foos that match a dynamic number of Bar constraints. I could do this by dynamically generating a query with the proper number of joins:

SELECT *
FROM Foo F INNER JOIN
     Foo_Bar FB1 ON FB1.FooId = F.Id AND FB1.BarId= Y INNER JOIN
     Foo_Bar FB2 ON FB2.FooId = F.Id AND FB2.BarId= Z INNER JOIN
     --one inner join for each constraint

I'm wondering if there's a simpler way. I basically want a query like this:

SELECT *
FROM Foo F
WHERE (Y, Z, ...) IN (SELECT BarId FROM Foo_Bar WHERE FooId = F.Id)

Of course that's not valid SQL, but I'm wondering if the dynamic query is the only reasonably portable way to achieve the desired result.

You could do `JOIN Foo_Bar FB1 ON FB1.FooId = F.Id WHERE FB1.BarId IN (Y, Z,....)` — Lamak, Jun 20 '12 at 22:53

score 1 · Answer 1 · answered Jun 20 '12 at 22:54

1

Based on the information that you've given, the following query would get the data you need:

SELECT *
FROM Foo F INNER JOIN
     Foo_Bar FB ON FB1.FooId = F.Id
WHERE FB.BarId IN (Y, Z, ...)

answered Jun 20 '12 at 22:54

Dancrumb

26,597
10
74
130

Your suggestion matches all records where FB.BarId is Z OR Y OR ... I want all records of F having FB.BarId is Z AND Y AND ... Basically the dual of the query you provided. – naasking Jun 21 '12 at 04:47

score 1 · Accepted Answer · edited May 23 '17 at 12:20

I assume you want a relational division query. See this question with a lot of ways to do this: How to filter SQL results in a has-many-through relation

There are also performance tests in @Erwin's answer (for Postrgres) and you'll notice that dynamic ways (using many Joins or many EXISTS subqueries or many IN subqueries) perform faster than static queries with only a variable table or list.

Test in your server, with your data to be sure though. MySQL for example has some performance issues with IN subqueries, so I would expect it to perform faster with the JOIN versions.

Here's the "Erwin 1" query, translated in your tables:

SELECT f.*
FROM   Foo f
JOIN   (
   SELECT FooId 
   FROM   Foo_Bar
   WHERE  BarId IN (Y, Z, ...)               --- your list or table here
   GROUP  BY FooId
   HAVING COUNT(*) = @N                      --- the size of this list or table
   ) fb ON fb.FooId = f.Id;

Intriguing thread you linked to. I was sure there must be a way to write this sort of query using simple parameters instead of dynamic sql, and I think you pretty thoroughly answered that challenge, so thanks! — naasking, Jun 21 '12 at 04:55

SQL dynamic in query

2 Answers2