Hardware is fun but can’t do anything without the software to power it.  But getting important data can be hard.  Why is the query for getting all the drinks the system can make with what it has on hand hard?  Because you can have drinks that are possible based on substitutes.  An example of this would be if my drink requires Grey Goose Vodka but I only have Smirnoff, I still should be able to get the drink.  This requires a logic that seems normal for us but it a bit hard to express in SQL.

First, here is my database layout that is prevalent to this post.  I will be renaming AlcoholType to IngredientType also.

Lets take this in multiple parts.  First we need all the ingredient IDs of what the system has on the hookup table or is a valid substitute.  Once again, an example of this is if the system has Vodka, I should be able to make any drink that has a vodka ingredient type.

SELECT * FROM
	ingredients i
	INNER JOIN AlcoholTypes a ON i.AlcoholTypeID = a.AlcoholTypeID
	LEFT JOIN hookups h ON i.IngredientID = h.IngredientID
	where
	(
		h.hookupid IS NULL AND i.AlcoholTypeID IN
		(
			select a.AlcoholTypeID from 
				ingredients i
				INNER JOIN AlcoholTypes a ON i.AlcoholTypeID = a.AlcoholTypeID
				INNER JOIN hookups h ON i.IngredientID = h.IngredientID
		)
	)
	OR
	(
		h.hookupid IS NOT NULL
	)

The left join on the hook up table will let me know which ingredients aren’t actually in the system.  By seeing which don’t have a hookup ID, I can then do another query where the ingredients are not in the hook up table but are valid substitutes based off the ingredient type.

Here is a sample output from this query with these items in the hookup table:

The system thinks it has Jim Bean, Cola, Orange Juice, and Smirnoff in it.  As you can see, I get back Grey Goose and Costco vodka as well since I have Smirnoff too even though they aren’t hooked into the system.

This then leads to this monster.  There is a small tweak from the query above to get this to execute.  I couldn’t use all the IN’s and had to switch to EXISTS in SQL so this lead to a small tweak in how the query looks.

SELECT * FROM drinks
WHERE drinkid NOT IN
(
	SELECT dr.drinkid FROM
	DrinkRecipes dr
	WHERE NOT EXISTS
	(
		SELECT * FROM
		ingredients i
		INNER JOIN AlcoholTypes a ON i.AlcoholTypeID = a.AlcoholTypeID
		LEFT JOIN hookups h ON i.IngredientID = h.IngredientID
		WHERE
		(( h.hookupid IS NULL AND EXISTS
			(
				select a.AlcoholTypeID from 
				ingredients ii
				INNER JOIN AlcoholTypes a ON ii.AlcoholTypeID = a.AlcoholTypeID
				INNER JOIN hookups h ON ii.IngredientID = h.IngredientID
				where i.AlcoholTypeID = a.AlcoholTypeID
			)
		) OR ( h.hookupid IS NOT NULL ))
		AND dr.IngredientID = i.IngredientID
	)
	GROUP BY dr.drinkid
)

And with the same items above, here is what I can make with the limited test data I have.

Now that I do have this logic, I bet I could make this query into a LINQ statement …  And yes, I’m betting there is a better way to do this in SQL.  If there is, please do tell.

I did my querying in Microsoft SQL Server Management Studio which you can get with the Express SKU and the database map was done with Visual Studio which you still should be able to do in Express