Styjun

Why does this if-statement combining assignment and an equality check return true?

Pirate democracy at its finest

Construct a word ladder

How strong are Wi-Fi signals?

Why do most published works in medical imaging try to reduce false positives?

Any advice on creating fictional locations in real places when writing historical fiction?

Grammar Question Regarding "Are the" or "Is the" When Referring to Something that May or May not be Plural

How to respond to an upset student?

What are the real benefits of using Salesforce DX?

Popcorn is the only acceptable snack to consume while watching a movie

Where's this lookout in Nova Scotia?

How can I tell if I'm being too picky as a referee?

Python program to take in two strings and print the larger string

Would Jetfuel for a modern jet like an F-16 or a F-35 be producable in the WW2 era?

A steel cutting sword?

How to Pin Point Large File eating space in Fedora 18

Count rotary dial pulses in a phone number (including letters)

I know that there is a preselected candidate for a position to be filled at my department. What should I do?

When the Torah was almost lost and one (or several) Rabbis saved it?

Who will lead the country until there is a new Tory leader?

Could a 19.25mm revolver actually exist?

Can a person survive on blood in place of water?

Externally monitoring CPU/SSD activity without software access

High resistance, no current. What's the point of a potential then?

Select name that contains ALL mentioned values and excludes duplicates in many-to-many relationship

SQL question conjunctionHow can I find which tables reference a given table in Oracle SQL Developer?INSTEAD OF UPDATE Trigger and Updating the Primary KeyFind all tables containing column with specified name - MS SQL ServerHow to structure a database with multiple join tablesWill not specifying a foreign key constraint get me in trouble?sql server executing update it takes more timeMySql and creating Foreign keysadding attributes to join_table from primary formASP.NET C# - Insert into compound keysInsert into table as many results row returns from subquery along with outer query result

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

I am fairly new to SQL and I am building a recipe database that takes in ingredients from a user and find recipes based on the ingredients.

I have an SQL query that gets recipe names that contain the ingredients, but I need it to select recipes that contain ALL the ingredients and excludes duplicates.

" SELECT recipes.Name, recipes.Preperation_Time, recipes.Author FROM recipes" +
" INNER JOIN RecipeIngredients ON RecipeIngredients.Recipe_ID = recipes.Recipe_ID" +
" INNER JOIN Ingredients ON Ingredients.Ingredient_ID = RecipeIngredients.Ingredient_ID" +
" WHERE ingredients.Name IN (" + ingredientString + ")");

Here are my tables if it helps:

CREATE TABLE [dbo].[recipes]
(
 [Recipe_ID] INT IDENTITY (1, 1) NOT NULL,
 [Name] VARCHAR(MAX) NOT NULL,
 [Instructions] TEXT NULL,
 [Preperation_Time] FLOAT(53) NULL,
 [Author] VARCHAR(MAX) NULL,

 CONSTRAINT [PK.recipes] 
 PRIMARY KEY CLUSTERED ([Recipe_ID] ASC)
 );

CREATE TABLE [dbo].[RecipeIngredients] 
(
 [Recipe_ID] INT NOT NULL,
 [Ingredient_ID] INT NOT NULL,

 PRIMARY KEY CLUSTERED ([Recipe_ID] ASC, [Ingredient_ID] ASC),
 CONSTRAINT [FK_RecipeIngredients_To_Ingredients] 
 FOREIGN KEY ([Ingredient_ID]) 
 REFERENCES [dbo].[Ingredients] ([Ingredient_ID]),
 CONSTRAINT [FK_RecipeIngredients_To_Recipes] 
 FOREIGN KEY ([Recipe_ID]) 
 REFERENCES [dbo].[recipes] ([Recipe_ID])
 );

CREATE TABLE [dbo].[Ingredients] 
(
 [Ingredient_ID] INT IDENTITY (1, 1) NOT NULL,
 [Name] VARCHAR(50) NOT NULL,

 PRIMARY KEY CLUSTERED ([Ingredient_ID] ASC)
);

Thanks.

sql sql-server tsql relational-division

share|improve this question

edited Mar 24 at 9:59

a_horse_with_no_name

314k47480583

asked Mar 24 at 3:38

goldenpenggoldenpeng

83

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This looks like SQL Server code, not MySQL. Also, please read about how to use prepared statements. Have you tried to solve this new query yourself yet?
  
  – Tim Biegeleisen
  Mar 24 at 3:41
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Possible duplicate of SQL question conjunction
  
  – Niayesh Isky
  Mar 24 at 4:03
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. See details here
  
  – marc_s
  Mar 24 at 7:07
  

  
  
  
  

add a comment | 

0

I am fairly new to SQL and I am building a recipe database that takes in ingredients from a user and find recipes based on the ingredients.

I have an SQL query that gets recipe names that contain the ingredients, but I need it to select recipes that contain ALL the ingredients and excludes duplicates.

" SELECT recipes.Name, recipes.Preperation_Time, recipes.Author FROM recipes" +
" INNER JOIN RecipeIngredients ON RecipeIngredients.Recipe_ID = recipes.Recipe_ID" +
" INNER JOIN Ingredients ON Ingredients.Ingredient_ID = RecipeIngredients.Ingredient_ID" +
" WHERE ingredients.Name IN (" + ingredientString + ")");

Here are my tables if it helps:

CREATE TABLE [dbo].[recipes]
(
 [Recipe_ID] INT IDENTITY (1, 1) NOT NULL,
 [Name] VARCHAR(MAX) NOT NULL,
 [Instructions] TEXT NULL,
 [Preperation_Time] FLOAT(53) NULL,
 [Author] VARCHAR(MAX) NULL,

 CONSTRAINT [PK.recipes] 
 PRIMARY KEY CLUSTERED ([Recipe_ID] ASC)
 );

CREATE TABLE [dbo].[RecipeIngredients] 
(
 [Recipe_ID] INT NOT NULL,
 [Ingredient_ID] INT NOT NULL,

 PRIMARY KEY CLUSTERED ([Recipe_ID] ASC, [Ingredient_ID] ASC),
 CONSTRAINT [FK_RecipeIngredients_To_Ingredients] 
 FOREIGN KEY ([Ingredient_ID]) 
 REFERENCES [dbo].[Ingredients] ([Ingredient_ID]),
 CONSTRAINT [FK_RecipeIngredients_To_Recipes] 
 FOREIGN KEY ([Recipe_ID]) 
 REFERENCES [dbo].[recipes] ([Recipe_ID])
 );

CREATE TABLE [dbo].[Ingredients] 
(
 [Ingredient_ID] INT IDENTITY (1, 1) NOT NULL,
 [Name] VARCHAR(50) NOT NULL,

 PRIMARY KEY CLUSTERED ([Ingredient_ID] ASC)
);

Thanks.

sql sql-server tsql relational-division

share|improve this question

edited Mar 24 at 9:59

a_horse_with_no_name

314k47480583

asked Mar 24 at 3:38

goldenpenggoldenpeng

83

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This looks like SQL Server code, not MySQL. Also, please read about how to use prepared statements. Have you tried to solve this new query yourself yet?
  
  – Tim Biegeleisen
  Mar 24 at 3:41
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Possible duplicate of SQL question conjunction
  
  – Niayesh Isky
  Mar 24 at 4:03
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. See details here
  
  – marc_s
  Mar 24 at 7:07
  

  
  
  
  

add a comment | 

I am fairly new to SQL and I am building a recipe database that takes in ingredients from a user and find recipes based on the ingredients.

I have an SQL query that gets recipe names that contain the ingredients, but I need it to select recipes that contain ALL the ingredients and excludes duplicates.

" SELECT recipes.Name, recipes.Preperation_Time, recipes.Author FROM recipes" +
" INNER JOIN RecipeIngredients ON RecipeIngredients.Recipe_ID = recipes.Recipe_ID" +
" INNER JOIN Ingredients ON Ingredients.Ingredient_ID = RecipeIngredients.Ingredient_ID" +
" WHERE ingredients.Name IN (" + ingredientString + ")");

Here are my tables if it helps:

CREATE TABLE [dbo].[recipes]
(
 [Recipe_ID] INT IDENTITY (1, 1) NOT NULL,
 [Name] VARCHAR(MAX) NOT NULL,
 [Instructions] TEXT NULL,
 [Preperation_Time] FLOAT(53) NULL,
 [Author] VARCHAR(MAX) NULL,

 CONSTRAINT [PK.recipes] 
 PRIMARY KEY CLUSTERED ([Recipe_ID] ASC)
 );

CREATE TABLE [dbo].[RecipeIngredients] 
(
 [Recipe_ID] INT NOT NULL,
 [Ingredient_ID] INT NOT NULL,

 PRIMARY KEY CLUSTERED ([Recipe_ID] ASC, [Ingredient_ID] ASC),
 CONSTRAINT [FK_RecipeIngredients_To_Ingredients] 
 FOREIGN KEY ([Ingredient_ID]) 
 REFERENCES [dbo].[Ingredients] ([Ingredient_ID]),
 CONSTRAINT [FK_RecipeIngredients_To_Recipes] 
 FOREIGN KEY ([Recipe_ID]) 
 REFERENCES [dbo].[recipes] ([Recipe_ID])
 );

CREATE TABLE [dbo].[Ingredients] 
(
 [Ingredient_ID] INT IDENTITY (1, 1) NOT NULL,
 [Name] VARCHAR(50) NOT NULL,

 PRIMARY KEY CLUSTERED ([Ingredient_ID] ASC)
);

Thanks.

sql sql-server tsql relational-division

share|improve this question

edited Mar 24 at 9:59

a_horse_with_no_name

314k47480583

asked Mar 24 at 3:38

goldenpenggoldenpeng

83

" SELECT recipes.Name, recipes.Preperation_Time, recipes.Author FROM recipes" +
" INNER JOIN RecipeIngredients ON RecipeIngredients.Recipe_ID = recipes.Recipe_ID" +
" INNER JOIN Ingredients ON Ingredients.Ingredient_ID = RecipeIngredients.Ingredient_ID" +
" WHERE ingredients.Name IN (" + ingredientString + ")");

CREATE TABLE [dbo].[recipes]
(
 [Recipe_ID] INT IDENTITY (1, 1) NOT NULL,
 [Name] VARCHAR(MAX) NOT NULL,
 [Instructions] TEXT NULL,
 [Preperation_Time] FLOAT(53) NULL,
 [Author] VARCHAR(MAX) NULL,

 CONSTRAINT [PK.recipes] 
 PRIMARY KEY CLUSTERED ([Recipe_ID] ASC)
 );

CREATE TABLE [dbo].[RecipeIngredients] 
(
 [Recipe_ID] INT NOT NULL,
 [Ingredient_ID] INT NOT NULL,

 PRIMARY KEY CLUSTERED ([Recipe_ID] ASC, [Ingredient_ID] ASC),
 CONSTRAINT [FK_RecipeIngredients_To_Ingredients] 
 FOREIGN KEY ([Ingredient_ID]) 
 REFERENCES [dbo].[Ingredients] ([Ingredient_ID]),
 CONSTRAINT [FK_RecipeIngredients_To_Recipes] 
 FOREIGN KEY ([Recipe_ID]) 
 REFERENCES [dbo].[recipes] ([Recipe_ID])
 );

CREATE TABLE [dbo].[Ingredients] 
(
 [Ingredient_ID] INT IDENTITY (1, 1) NOT NULL,
 [Name] VARCHAR(50) NOT NULL,

 PRIMARY KEY CLUSTERED ([Ingredient_ID] ASC)
);

sql sql-server tsql relational-division

sql sql-server tsql relational-division

share|improve this question

edited Mar 24 at 9:59

a_horse_with_no_name

314k47480583

asked Mar 24 at 3:38

goldenpenggoldenpeng

83

share|improve this question

edited Mar 24 at 9:59

a_horse_with_no_name

314k47480583

asked Mar 24 at 3:38

goldenpenggoldenpeng

83

edited Mar 24 at 9:59

a_horse_with_no_name

314k47480583

edited Mar 24 at 9:59

a_horse_with_no_name

314k47480583

asked Mar 24 at 3:38

goldenpenggoldenpeng

83

asked Mar 24 at 3:38

goldenpenggoldenpeng

83

1 Answer
1

active

oldest

votes

0

You can use aggregation. I would recommend passing in the ingredients as a VALUES() list rather than strings. However, with your construct:

SELECT r.Name, r.Preperation_Time, r.Author
FROM recipes r LEFT JOIN
 RecipeIngredients ri
 ON ri.Recipe_ID = r.Recipe_ID LEFT JOIN
 Ingredients i
 ON i.Ingredient_ID = ri.Ingredient_ID AND
 i.Name IN (" + ingredientString + ")"
GROUP BY r.Name, r.Preperation_Time, r.Author
HAVING COUNT(*) = COUNT(i.Ingredient_Id); -- all match

share|improve this answer

edited Mar 24 at 16:21

answered Mar 24 at 13:02

Gordon LinoffGordon Linoff

813k37327434

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This query works if the number_of_ingredients is exactly the same as the amount of ingredients in that specific recipe, but once more ingredients are added, no results are shown. If all ingredients are selected, all recipes need to be shown but at the moment it does not.
  
  – goldenpeng
  Mar 24 at 15:44
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @goldenpeng . . . I see what you are saying. You want to count matching ingredients.
  
  – Gordon Linoff
  Mar 24 at 16:22
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55320525%2fselect-name-that-contains-all-mentioned-values-and-excludes-duplicates-in-many-t%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

You can use aggregation. I would recommend passing in the ingredients as a VALUES() list rather than strings. However, with your construct:

SELECT r.Name, r.Preperation_Time, r.Author
FROM recipes r LEFT JOIN
 RecipeIngredients ri
 ON ri.Recipe_ID = r.Recipe_ID LEFT JOIN
 Ingredients i
 ON i.Ingredient_ID = ri.Ingredient_ID AND
 i.Name IN (" + ingredientString + ")"
GROUP BY r.Name, r.Preperation_Time, r.Author
HAVING COUNT(*) = COUNT(i.Ingredient_Id); -- all match

share|improve this answer

edited Mar 24 at 16:21

answered Mar 24 at 13:02

Gordon LinoffGordon Linoff

813k37327434

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This query works if the number_of_ingredients is exactly the same as the amount of ingredients in that specific recipe, but once more ingredients are added, no results are shown. If all ingredients are selected, all recipes need to be shown but at the moment it does not.
  
  – goldenpeng
  Mar 24 at 15:44
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @goldenpeng . . . I see what you are saying. You want to count matching ingredients.
  
  – Gordon Linoff
  Mar 24 at 16:22
  

  
  
  
  

add a comment | 

0

You can use aggregation. I would recommend passing in the ingredients as a VALUES() list rather than strings. However, with your construct:

SELECT r.Name, r.Preperation_Time, r.Author
FROM recipes r LEFT JOIN
 RecipeIngredients ri
 ON ri.Recipe_ID = r.Recipe_ID LEFT JOIN
 Ingredients i
 ON i.Ingredient_ID = ri.Ingredient_ID AND
 i.Name IN (" + ingredientString + ")"
GROUP BY r.Name, r.Preperation_Time, r.Author
HAVING COUNT(*) = COUNT(i.Ingredient_Id); -- all match

share|improve this answer

edited Mar 24 at 16:21

answered Mar 24 at 13:02

Gordon LinoffGordon Linoff

813k37327434

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This query works if the number_of_ingredients is exactly the same as the amount of ingredients in that specific recipe, but once more ingredients are added, no results are shown. If all ingredients are selected, all recipes need to be shown but at the moment it does not.
  
  – goldenpeng
  Mar 24 at 15:44
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @goldenpeng . . . I see what you are saying. You want to count matching ingredients.
  
  – Gordon Linoff
  Mar 24 at 16:22
  

  
  
  
  

add a comment | 

You can use aggregation. I would recommend passing in the ingredients as a VALUES() list rather than strings. However, with your construct:

SELECT r.Name, r.Preperation_Time, r.Author
FROM recipes r LEFT JOIN
 RecipeIngredients ri
 ON ri.Recipe_ID = r.Recipe_ID LEFT JOIN
 Ingredients i
 ON i.Ingredient_ID = ri.Ingredient_ID AND
 i.Name IN (" + ingredientString + ")"
GROUP BY r.Name, r.Preperation_Time, r.Author
HAVING COUNT(*) = COUNT(i.Ingredient_Id); -- all match

share|improve this answer

edited Mar 24 at 16:21

answered Mar 24 at 13:02

Gordon LinoffGordon Linoff

813k37327434

SELECT r.Name, r.Preperation_Time, r.Author
FROM recipes r LEFT JOIN
 RecipeIngredients ri
 ON ri.Recipe_ID = r.Recipe_ID LEFT JOIN
 Ingredients i
 ON i.Ingredient_ID = ri.Ingredient_ID AND
 i.Name IN (" + ingredientString + ")"
GROUP BY r.Name, r.Preperation_Time, r.Author
HAVING COUNT(*) = COUNT(i.Ingredient_Id); -- all match

share|improve this answer

edited Mar 24 at 16:21

answered Mar 24 at 13:02

Gordon LinoffGordon Linoff

813k37327434

answered Mar 24 at 13:02

Gordon LinoffGordon Linoff

813k37327434

answered Mar 24 at 13:02

Gordon LinoffGordon Linoff

813k37327434