Scenario: Consider a restaurant in which whenever new items have to be added to the menu, the items have to be first approved or denied by the restaurant manager. The items that are denied or have a blank response are stored in a table called denials which is shown below.
Upon further examination, it is observed that some entries have a blank response because, for some items, the manager leaves the response as blank and instead adds another row manually and then marks that row as denied.
For example, the manager left blank responses for strawberry cheesecake and raspberry cheesecake and added a new row with the item berry flavored cheesecake and marked it as denied. This manual entry accounts for denials of both strawberry and raspberry cheesecake. We see similar examples in other groups, for instance, double carrot cake and tomato cake are collectively marked as denied by creating a new manual entry row for veggie flavored cake.
The requirement is that for each row with a blank response, we need to find the equivalent item which has a denied response. In order to tackle this problem, we have already created a keyword lookup table called lookup that contains the keyword which needs to be searched for in the item column in order to find the matching item with a denied response.
The final desired output is shown below:
Note: The resulting matching item should be within the same group. For example, in group D, cranberry cheesecake has a blank response. The lookup keyword for cranberry cheesecake is berry. So if we ignore the group column, then the matching item would be berry flavored cheesecake. However, the scope for searching has to be within the same group i.e. group D. Since group D does not have any item that matches the lookup keyword berry, the expected result is no response.
I am not able to figure out how to get the desired output. I need help with that.
Below is the SQL script to create the schema and data for denails and lookup tables:
CREATE TABLE [denials](
[group] [nvarchar](50) NOT NULL,
[item] [nvarchar](255) NOT NULL,
[manager_response] [nvarchar](255) NULL
)
CREATE TABLE [lookup](
[item] [nvarchar](255) NOT NULL,
[lookup_keyword] [nvarchar](255) NULL
)
INSERT [denials] ([group], [item], [manager_response]) VALUES ('A', 'lemon cheesecake', 'denied')
INSERT [denials] ([group], [item], [manager_response]) VALUES ('A', 'strawberry cheesecake', NULL)
INSERT [denials] ([group], [item], [manager_response]) VALUES ('A', 'raspberry cheesecake', NULL)
INSERT [denials] ([group], [item], [manager_response]) VALUES ('A', 'berry flavored cheesecake', 'denied')
INSERT [denials] ([group], [item], [manager_response]) VALUES ('B', 'apple cheesecake', 'denied')
INSERT [denials] ([group], [item], [manager_response]) VALUES ('B', 'blueberry cheesecake', 'denied')
INSERT [denials] ([group], [item], [manager_response]) VALUES ('B', 'orange cheesecake', 'denied')
INSERT [denials] ([group], [item], [manager_response]) VALUES ('B', 'double carrot cake', NULL)
INSERT [denials] ([group], [item], [manager_response]) VALUES ('B', 'tomato cake', NULL)
INSERT [denials] ([group], [item], [manager_response]) VALUES ('B', 'veggie flavored cake', 'denied')
INSERT [denials] ([group], [item], [manager_response]) VALUES ('C', 'red grapes cheesecake', NULL)
INSERT [denials] ([group], [item], [manager_response]) VALUES ('C', 'green grapes cheesecake', NULL)
INSERT [denials] ([group], [item], [manager_response]) VALUES ('C', 'grape flavored cheesecake', 'denied')
INSERT [denials] ([group], [item], [manager_response]) VALUES ('D', 'cranberry cheesecake', NULL)
INSERT [denials] ([group], [item], [manager_response]) VALUES ('D', 'cinnamon cheesecake', 'denied')
INSERT [lookup] ([item], [lookup_keyword]) VALUES ('strawberry cheesecake', 'berry')
INSERT [lookup] ([item], [lookup_keyword]) VALUES ('raspberry cheesecake ', 'berry')
INSERT [lookup] ([item], [lookup_keyword]) VALUES ('double carrot cake', 'veggie')
INSERT [lookup] ([item], [lookup_keyword]) VALUES ('tomato cake', 'veggie')
INSERT [lookup] ([item], [lookup_keyword]) VALUES ('red grapes cheesecake', 'grape')
INSERT [lookup] ([item], [lookup_keyword]) VALUES ('green grapes cheesecake', 'grape')
INSERT [lookup] ([item], [lookup_keyword]) VALUES ('cranberry cheesecake', 'berry')
