title: Select from 3 tables (one to many, many to one relationship)
body: <p>I have 3 tables: 
  Recipes (1 --- * ) Ingridients ( *---1) Products. I need to obtain Recipes that contains products in a given list or products that are not in list but have a specific flag set. I have a flag in product table (bool). So where clause looks like:</p>
  
  <pre><code>WHERE Product.Caption IN ('A', 'B', 'C') OR (Product.Caption NOT IN ('A', 'B', 'C') AND Product.Flag=TRUE)
  </code></pre>
  
  <p>Important is: I do not need recipes that contain products in list and also contain other products (not in list and flag is false).</p>
  
  <p>Bellow is an example database dump for MSSQL:</p>
  
  <pre><code>USE [master]
  IF EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE name = N'movedb') ALTER DATABASE [movedb] SET SINGLE_USER With ROLLBACK IMMEDIATE
  IF EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE name = N'movedb') DROP DATABASE [movedb]
  IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE name = N'movedb') CREATE DATABASE [movedb]
  USE [movedb]
  
  --
  -- Table structure for table 'Ingridients'
  --
  
  IF object_id(N'Ingridients', 'U') IS NOT NULL DROP TABLE [Ingridients]
  
  CREATE TABLE [Ingridients] (
    [Id] INT NOT NULL IDENTITY, 
    [Quantity] INT DEFAULT 0, 
    [IdProduct] INT DEFAULT 0, 
    [IdRecipe] INT DEFAULT 0, 
    PRIMARY KEY ([Id])
  )
  
  SET IDENTITY_INSERT [Ingridients] ON
  GO
  
  --
  -- Dumping data for table 'Ingridients'
  --
  
  INSERT INTO [Ingridients] ([Id], [Quantity], [IdProduct], [IdRecipe]) VALUES (1, 0, 1, 2)
  INSERT INTO [Ingridients] ([Id], [Quantity], [IdProduct], [IdRecipe]) VALUES (2, 0, 3, 2)
  INSERT INTO [Ingridients] ([Id], [Quantity], [IdProduct], [IdRecipe]) VALUES (3, 0, 4, 2)
  INSERT INTO [Ingridients] ([Id], [Quantity], [IdProduct], [IdRecipe]) VALUES (4, 0, 6, 2)
  INSERT INTO [Ingridients] ([Id], [Quantity], [IdProduct], [IdRecipe]) VALUES (5, 0, 2, 3)
  INSERT INTO [Ingridients] ([Id], [Quantity], [IdProduct], [IdRecipe]) VALUES (6, 0, 4, 3)
  INSERT INTO [Ingridients] ([Id], [Quantity], [IdProduct], [IdRecipe]) VALUES (7, 0, 8, 3)
  INSERT INTO [Ingridients] ([Id], [Quantity], [IdProduct], [IdRecipe]) VALUES (8, 0, 1, 4)
  INSERT INTO [Ingridients] ([Id], [Quantity], [IdProduct], [IdRecipe]) VALUES (9, 0, 6, 4)
  INSERT INTO [Ingridients] ([Id], [Quantity], [IdProduct], [IdRecipe]) VALUES (10, 0, 5, 4)
  -- 10 records
  
  SET IDENTITY_INSERT [Ingridients] OFF
  GO
  
  --
  -- Table structure for table 'Products'
  --
  
  IF object_id(N'Products', 'U') IS NOT NULL DROP TABLE [Products]
  
  CREATE TABLE [Products] (
    [Id] INT NOT NULL IDENTITY, 
    [Caption] NVARCHAR(255), 
    [EasyToFind] BIT DEFAULT 0, 
    PRIMARY KEY ([Id])
  )
  
  SET IDENTITY_INSERT [Products] ON
  GO
  
  --
  -- Dumping data for table 'Products'
  --
  
  INSERT INTO [Products] ([Id], [Caption], [EasyToFind]) VALUES (1, N'ProductA', 0)
  INSERT INTO [Products] ([Id], [Caption], [EasyToFind]) VALUES (2, N'ProductB', 0)
  INSERT INTO [Products] ([Id], [Caption], [EasyToFind]) VALUES (3, N'ProductC', 0)
  INSERT INTO [Products] ([Id], [Caption], [EasyToFind]) VALUES (4, N'ProductD', -1)
  INSERT INTO [Products] ([Id], [Caption], [EasyToFind]) VALUES (5, N'ProductE', 0)
  INSERT INTO [Products] ([Id], [Caption], [EasyToFind]) VALUES (6, N'ProductF', -1)
  INSERT INTO [Products] ([Id], [Caption], [EasyToFind]) VALUES (7, N'ProductG', 0)
  INSERT INTO [Products] ([Id], [Caption], [EasyToFind]) VALUES (8, N'ProductH', 0)
  INSERT INTO [Products] ([Id], [Caption], [EasyToFind]) VALUES (9, N'ProductI', 0)
  INSERT INTO [Products] ([Id], [Caption], [EasyToFind]) VALUES (10, N'ProductJ', 0)
  -- 10 records
  
  SET IDENTITY_INSERT [Products] OFF
  GO
  
  --
  -- Table structure for table 'Recipes'
  --
  
  IF object_id(N'Recipes', 'U') IS NOT NULL DROP TABLE [Recipes]
  
  CREATE TABLE [Recipes] (
    [Id] INT NOT NULL IDENTITY, 
    [Caption] NVARCHAR(255), 
    PRIMARY KEY ([Id])
  )
  
  SET IDENTITY_INSERT [Recipes] ON
  GO
  
  --
  -- Dumping data for table 'Recipes'
  --
  
  INSERT INTO [Recipes] ([Id], [Caption]) VALUES (2, N'RecipeA')
  INSERT INTO [Recipes] ([Id], [Caption]) VALUES (3, N'RecipeB')
  INSERT INTO [Recipes] ([Id], [Caption]) VALUES (4, N'RecipeC')
  -- 3 records
  
  SET IDENTITY_INSERT [Recipes] OFF
  GO
  </code></pre>
  
  <p>Example:
  If I search for ProductA and ProductE it should give me only RecipeC</p>
  
  <p>Right now I have something like this for MySQL ( it is not final. I can only operate with Ids, I neet somehow to change it to work only with product captions and adapt for MSSQL)</p>
  
  <pre><code>SELECT 
      *
  FROM
      recipes AS r
          INNER JOIN
      ingridients i ON i.IdRecipe = r.Id
  WHERE
      i.IdProduct IN (1 , 5, 6)
  GROUP BY r.Id
  HAVING COUNT(*) = (SELECT 
          COUNT(*)
      FROM
          ingridients AS ing
      WHERE
          ing.IdRecipe = r.Id);
  </code></pre>