dynamic Sql or sql algorithm

Question

I have 3 tables. The story is: I will choose materials. And which meals can be made with these ingredients.

And this is my c# codes.

string sql = "SELECT * FROM tblmealmaterials WHERE ";
        string[] idler = textBox1.Text.Split(',');

        for (int i = 0; i < idler.Length; i++)
        {
            for (int s = 0; s < i; s++)
            {
                sql +="( materialId= "+ idler[s] + " and materialId= " + idler[i] +  " )" ;

                if (s<i)
                    sql += " or ";

            }
        }

        sql += " (";
        for (int i = 0; i < idler.Length; i++)
        {
            sql += " materialId="+ idler[i] ;
            if (i<idler.Length-1)
                sql += " and ";
        }
        sql += " )";                

        richTextBox1.Text = sql;

my tables:

my sql generator codes c#

What problem you are facing with the current code? Do you know what is the problem with the query which is currently generated? — Chetan, Jun 28 '17 at 12:49
@tuncgulec That's still not a question... we have absolutely no idea what you're trying to do... — Siyual, Jun 28 '17 at 12:49
See also https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection — mjwills, Jun 28 '17 at 12:50
what does "with selected materials" mean? must it use all of those and none others? all of those and possibly some others? at least 1 of those and possibly some others? at least some of those but nothing that isn't specified? — Marc Gravell, Jun 28 '17 at 12:53
post sample data from your tables and also post the result you expect from your query — GuidoG, Jun 28 '17 at 12:53
ultimately, this is an algo problem, not a coding one; first you need to figure out *what you need to do*; forget about code: how would you answer it if it were (say) pieces of paper in buckets? — Marc Gravell, Jun 28 '17 at 12:53
Actually; I need to find out which dishes are made from selected materials. Selected material Id: 1 -2 -3. Accordingly: 1,2,3 or 1,2 or 1,3. — tuncgulec, Jun 28 '17 at 12:58
Recipe A needs 1,2,3,4. Recipe B needs 1,2,4. Recipe C needs 1,2. Recipe D needs 1,2,3. The user enters 1,2,3 into your text box. Which recipes of those do you expect to see returned? — mjwills, Jun 28 '17 at 13:01

mjwills · Accepted Answer · 2017-06-28T13:22:58.140

0

SELECT b.MealID FROM 
(
SELECT mealid, COUNT(*) as IngredientCount FROM tblmealmaterials WHERE materialid in (1,2,3)
GROUP BY mealid
)
a
INNER JOIN tblmealmaterials b
ON a.mealID = b.mealID 
GROUP BY b.MealID
HAVING COUNT(*) = MAX(a.IngredientCount)

The above query will find meals that use any of the ingredients (with the IN clause), and then verify that the recipe only uses those ingredients (by comparing the COUNTs).

To avoid SQL injection issues (and to simplify writing the IN query), I'd recommend using parameterised queries via an ORM. PetaPoco provides nice IN support.

edited Jun 28 '17 at 13:22

answered Jun 28 '17 at 13:15

mjwills

23,389
6
40
63

Thank you. But : "Column 'a.IngredientCount' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause." – tuncgulec Jun 28 '17 at 13:21

dynamic Sql or sql algorithm

1 Answers1