2

This is SQL Server code.

Let's say you have a table with three columns. Column 1 is named Monster, Column 2 is named Level, and Column 3 is named BodyType. Level signifies how powerful the monster is and BodyType signifies what type of body it has.

My schema:

CREATE TABLE YourTable
    ([Monster] nvarchar(max), [Level] int, [BodyType] nvarchar(max))
;

INSERT INTO YourTable
    ([Monster], [Level], [BodyType])
VALUES
    ('Small Beast', 300, 'Scaly'),
    ('Large Beast', 700, 'Slimy'),
    ('Small Dragon', 350, 'Fiery'),
    ('Large Dragon', 800, 'Slimy')
;

I have a sql command to find all possible combinations of the monsters. It uses recursive cte because the number of monsters in the table can fluctuate(so I can add more monsters later on). The command also grabs the sum total value of the Level of the monsters that are being combined. The command also only outputs the combinations of monsters that fall under a certain total sum value. In this example, that total sum value is 1500. Everything up to this point works as it should.

My sql command:

;WITH cte AS (
SELECT  Monster, 
        [Level],
        BodyType,
        1 as l
FROM YourTable
UNION ALL


SELECT  c1.Monster+','+c2.Monster,
        c1.[Level]+c2.[Level],
        c1.BodyType+','+c2.BodyType,
        c1.l+1
FROM cte c1
CROSS JOIN YourTable c2
WHERE c1.Monster NOT LIKE '%'+c2.Monster+'%'
)


SELECT *
FROM cte
WHERE cte.Level < 1500
ORDER BY l
OPTION (MAXRECURSION 0)

And the correct output:

1   Small Beast 300 Scaly   1
2   Large Beast 700 Slimy   1
3   Small Dragon    350 Fiery   1
4   Large Dragon    800 Slimy   1
5   Large Dragon,Small Beast    1100    Slimy,Scaly 2
6   Large Dragon,Small Dragon   1150    Slimy,Fiery 2
7   Small Dragon,Small Beast    650 Fiery,Scaly 2
8   Small Dragon,Large Beast    1050    Fiery,Slimy 2
9   Small Dragon,Large Dragon   1150    Fiery,Slimy 2
10  Large Beast,Small Beast 1000    Slimy,Scaly 2
11  Large Beast,Small Dragon    1050    Slimy,Fiery 2
12  Small Beast,Large Beast 1000    Scaly,Slimy 2
13  Small Beast,Small Dragon    650 Scaly,Fiery 2
14  Small Beast,Large Dragon    1100    Scaly,Slimy 2
15  Small Beast,Large Dragon,Small Dragon   1450    Scaly,Slimy,Fiery   3
16  Small Beast,Small Dragon,Large Beast    1350    Scaly,Fiery,Slimy   3
17  Small Beast,Small Dragon,Large Dragon   1450    Scaly,Fiery,Slimy   3
18  Small Beast,Large Beast,Small Dragon    1350    Scaly,Slimy,Fiery   3
19  Large Beast,Small Dragon,Small Beast    1350    Slimy,Fiery,Scaly   3
20  Large Beast,Small Beast,Small Dragon    1350    Slimy,Scaly,Fiery   3
21  Small Dragon,Large Dragon,Small Beast   1450    Fiery,Slimy,Scaly   3
22  Small Dragon,Large Beast,Small Beast    1350    Fiery,Slimy,Scaly   3
23  Small Dragon,Small Beast,Large Beast    1350    Fiery,Scaly,Slimy   3
24  Small Dragon,Small Beast,Large Dragon   1450    Fiery,Scaly,Slimy   3
25  Large Dragon,Small Dragon,Small Beast   1450    Slimy,Fiery,Scaly   3
26  Large Dragon,Small Beast,Small Dragon   1450    Slimy,Scaly,Fiery   3

The problem I am encountering is when I add a Where clause to only bring back monsters that are not of a certain body type(BodyType column). That part of the code from above when modified to accomplish this is:

;WITH cte AS (
SELECT  Monster, 
        [Level],
        BodyType,
        1 as l
FROM YourTable
WHERE BodyType NOT LIKE 'Fiery' AND BodyType NOT LIKE 'Slimy'
UNION ALL

The output becomes the following which is incorrect because it still includes the body types of Slimy and Fiery: 

    Monster Level   BodyType    l
1   Small Beast 300 Scaly   1
2   Small Beast,Large Beast 1000    Scaly,Slimy 2
3   Small Beast,Small Dragon    650 Scaly,Fiery 2
4   Small Beast,Large Dragon    1100    Scaly,Slimy 2
5   Small Beast,Large Dragon,Small Dragon   1450    Scaly,Slimy,Fiery   3
6   Small Beast,Small Dragon,Large Beast    1350    Scaly,Fiery,Slimy   3
7   Small Beast,Small Dragon,Large Dragon   1450    Scaly,Fiery,Slimy   3
8   Small Beast,Large Beast,Small Dragon    1350    Scaly,Slimy,Fiery   3

The output seems to be working partially since Large Beast is Slimy and it ignored it the first time but I suspect it is ignoring the NOT LIKE clause when moving through the levels of BodyType and that's why it doesn't ignore Large Beast on subsequent finds.

Kian
  • 389
  • 1
  • 2
  • 15

3 Answers3

2

If i understand correctly, you could try 2 approaches below:

1.Filter bloodtype after recursive cte

;WITH cte AS (
SELECT  Monster, 
        [Level],
        BodyType,
        1 as l
FROM YourTable
UNION ALL

SELECT  c1.Monster+','+c2.Monster,
        c1.[Level]+c2.[Level],
        c1.BodyType+','+c2.BodyType,
        c1.l+1
FROM cte c1
CROSS JOIN YourTable c2
WHERE c1.Monster NOT LIKE '%'+c2.Monster+'%'
)
SELECT *
FROM cte
WHERE cte.Level < 1500
AND ',' + cte.BodyType ',' + NOT LIKE '%,Fiery,%' 
AND ',' + cte.BodyType ','  + NOT LIKE '%,Slimy,%'
ORDER BY l
OPTION (MAXRECURSION 0)

2.Use the second cte to filter before recursive cte

;WITH temp AS
(
    SELECT * 
    FROM YourTable
    WHERE   BodyType != 'Fiery' 
            AND BodyType != 'Slimy'
)
,cte AS (
SELECT  Monster, 
        [Level],
        BodyType,
        1 as l
FROM temp
UNION ALL

SELECT  c1.Monster+','+c2.Monster,
        c1.[Level]+c2.[Level],
        c1.BodyType+','+c2.BodyType,
        c1.l+1
FROM cte c1
CROSS JOIN temp c2
WHERE c1.Monster NOT LIKE '%'+c2.Monster+'%'
)
SELECT *
FROM cte
WHERE cte.Level < 1500   
ORDER BY l
OPTION (MAXRECURSION 0)
TriV
  • 5,118
  • 2
  • 10
  • 18
  • The second solution seems to work great. :) The first solution was missing some output values when I tested it with additional schema data added. I think it may not be going through iterations after level 2. Here is your working solution: http://rextester.com/live/UNCPIY56111 Here is your other solution that ignores a lot of valid results: http://rextester.com/live/PKOVR79091 – Kian May 27 '17 at 17:51
  • I just have seen your testing data. I tweak the first query and you could try it http://rextester.com/QZZQVX23133 – TriV May 27 '17 at 18:06
  • Hey that works. But I noticed it takes almost three to four times as long to process on server side. I think I will go with your first working solution. Btw, can you email me at kiangraphy[at]gmail.com I just have one follow up question I don't think is appropriate here. – Kian May 27 '17 at 18:17
  • If you are concerned about performance then don't use varchar (max) and don't use unicode (nvarchar) - your data doesn't appear to need either of those. – Lord Peter May 27 '17 at 20:12
0

You need AND in your logic, not OR:

WHERE BodyType NOT LIKE 'Fiery' AND BodyType NOT LIKE 'Slimy'

A BodyType of "Fiery" is not like "Slimy". So, it fits the second condition. Note that if you use LIKE, then you want AND.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • That's what I had it as at first. But I thought OR would help it ignore both Fiery and Slimy if it came across both at once. The solution still doesn't work your way. You should try it out at http://rextester.com/l/sql_server_online_compiler if you don't believe me. – Kian May 27 '17 at 16:25
0

I think I found a solution. Output data seems to be correct:

SELECT  c1.Monster+','+c2.Monster,
        c1.[Level]+c2.[Level],
        c1.BodyType+','+c2.BodyType,
        c1.l+1
FROM cte c1
CROSS JOIN YourTable c2
WHERE (c1.Monster NOT LIKE '%'+c2.Monster+'%') AND (c1.BodyType NOT LIKE 'Scaly' AND c2.BodyType NOT LIKE 'Scaly') AND (c1.BodyType NOT LIKE 'Fiery' AND c2.BodyType NOT LIKE 'Fiery') 
)

I took the broken Where clause out and just added NOT LIKE's to the Where clause after the CROSS JOIN from the original code.

Just not sure if this isn't a best practice or could break something, anyone want to chime in? Thank you.

Edit: My solution has one problem where the first iteration will include ignored 'BodyTypes'

Kian
  • 389
  • 1
  • 2
  • 15