Sql query with many to many tables

Question

I'm using VB.Net express with an Access file, and I have the following tables:

table Formula
id | name
-------------------
1  | formula 1
2  | formula 2
3  | formula 3


table Component
id | name
--------------------
1  | A
2  | B
3  | C
4  | D


table FormulaComponents
formula_id | component_id
-------------------------
1   |  1
1   |  2
1   |  4
2   |  1
2   |  3
2   |  4
3   |  1
3   |  2
3   |  3

So each formula have one or more components.

Which query will I use if I want all the formulas with for example Component A AND Component D (Result: formula 1, formula 2)? I try something with intersect, but it seems it doesn't work in VB...

Thanks!

Do you want all the formulas with ONLY A and D or that may have other items as well? — Raj More, Dec 22 '10 at 15:05

D'Arcy Rittich · Answer 1 · 2010-12-23T15:19:08.167

2

Update:

select f.*
from (
    select c.id
    from FormulaComponents fc
    inner join Component c on fc.component_id = c.id
    where c.name in ('A', 'B')
    group by c.id
    having count(distinct c.name) = 2
) c2 
inner join FormulaComponents fc on c2.id = fc.component_id 
inner join Formula f on fc.formula_id = f.id

edited Dec 23 '10 at 15:19

answered Dec 22 '10 at 15:12

D'Arcy Rittich

167,292
40
290
283

.. See what you can come up with for this question http://stackoverflow.com/questions/4510185/select-max-value-of-each-group/4510200#4510200 . As I dont think I actually helped him. – John Hartsock Dec 22 '10 at 15:31
Thanks, but this query returns components, I need the formulas where the components are into... – Philippe Gonday Dec 23 '10 at 09:56

score 0 · Answer 2 · answered Dec 22 '10 at 15:04

SELECT DISTINCT
  f.*
FROM Formula f
INNER JOIN FormulaComponent fc on fc.formula_id = f.formula_id
INNER JOIN Component c on c.component_id = fc.componentid
WHERE Exists (SELECT * FROM FormulaComponent fc1 WHERE fc1.formulaID = f.formulaId AND c.Name = 'A')
  AND Exists (SELECT * FROM FormulaComponent fc1 WHERE fc1.formulaID = f.formulaId AND c.Name = 'D')

Sql query with many to many tables

2 Answers2