Select data from tables

Question

I have to SQL tables:

Table 1 (Recipes):

Name        Ingredients
----------------------------------
ciorba      apa,sare,piper,branza  
paste       branza,oua,lapte   

Table 2 (Ingredients liked by user):

Name       Ingredients
-------------------------
ionutG     branza  
vasile     oua,lapte  

I want to select recipes from table 1 that contains one ore more liked ingredients from table 2.

Ex : For ionutG I want to retrieve both recipes because there are liked ingredients. And for vasile I want to retrieve only the second recipe.

Note that the tables are larger and there are so many entries.

Here is what I tried:

SELECT 
    a.Nume 
FROM
    reteta a 
JOIN 
    ingredientplacut b ON (a.Ingrediente = b.Ingrediente);  

But it returns me only the recipes that have exact the same ingredients which I don't want. I want to return the recipes that have one or multiple liked ingredients not all.

Thanks

Answer 1

Try exists Like for example

 Select a.name from table 1 where exists (select b.name from table 2 where a.ingredient = b.name); 

Please note that this might not be correct syntax. So it returns all recipes where exists will return true. So where one or more ingredients are same.

Answer 2

If you insist on storing data like you do, a possible solution (tested in T-SQL, question does not specify SQL flavor):

Setup

create table Recipes 
(
    RecipeId INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_Recipes PRIMARY KEY,
    Name NVARCHAR(100),
    Ingredients NVARCHAR(4000)
)
GO

insert into Recipes (Name, Ingredients) VALUES ('ciorba', 'apa,sare,piper,branza'), ('paste', 'branza,oua,lapte')
GO


create table Ingredients
(
    IngredientId INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_Ingredients PRIMARY KEY,
    Name NVARCHAR(100),
    Ingredients NVARCHAR(4000)
)
GO

insert into Ingredients (Name, Ingredients) VALUES ('ionutG', 'branza'), ('vasile', 'oua,lapte')
GO

-- cannot remember what is the source for this - probably this

CREATE FUNCTION [dbo].[SplitStrings_XML]
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );

GO

select * from Recipes
select * from Ingredients
GO

-- split strings and retrieve records where intersection is not null
select distinct I.Name, I.Ingredients, R.Name 
FROM Ingredients I
    CROSS APPLY dbo.SplitStrings_XML(Ingredients, ',') IToken
JOIN Recipes R
    CROSS APPLY dbo.SplitStrings_XML(Ingredients, ',') RToken
        ON RToken.Item = IToken.Item

Note:. As already mentioned, you should really consider normalizing your data:

1) Performance - string operations are expensive and they should be avoided

2) Hard to write queries - a basic query for finding out common items uses a user defined function

3) Bad for application development - consider that the client also requires an English version for the application. Currently, all items are in Romanian. How to you handle this situation?

4) Bad for reports - consider that your structure requires extensive querying for reports regarding ingredients usage and other information. Those reports will have complex and messy queries which will be slow

[EDIT] For MySQL the following references may be used to replace unsupported features shown above:

Split strings using a method shown here. Related questions and answers here and here.