I cannot really understand your data model. Why do you have the item to sell "$2 sundae sale" in the same table as the modifiers?

As Jonas Metzler states in his comment, the issue may not only be with your query but also with your data. If I understand correctly your model correctly: There are items for sale (the $2 sundae) which the customer can modify. There are modifiers which are grouped into modifier groups. Finally based on the choice of modifier there may be an upcharge for the sale item.

If this is correct the following data model makes more sense and would simplify your queries (I use * to denote the PK).

The table with the items for sale ($2 sunday)

SALE_ITEMS
----------
* RECORD_KEY
NAME

The table with all the modifiers (COOKIE DOUGH IC, BLACK RASP IC, etc.)

MODIFIERS
---------
* RECORD_KEY
NAME

The table with the group of modifiers (Ice cream flavour)

MODIFIER_GROUPS 
---------------
* RECORD_KEY
NAME

The table that groups modifiers into groups (both columns form the primary key for the table).

MODIFIER_GROUPING
-----------------
* MODIFIER_GROUP_RECORD_KEY
* MODIFIER_RECORD_KEY

The table that lists which modifier groups can be applied to each sale item. The CHOOSE_LATER column is a boolean (0,1) to define whether the user can choose the modifier later (whatever later means). This is instead of a separate entry in the table with the upcharges.

ITEM_MODIFIERS
--------------
* SALE_ITEM_RECORD_KEY
* MODIFIER_GROUP_RECORD_KEY
CHOOSE_LATER

Table with the upcharge for sale item based on the modifier. Note that upcharge $0 can be the default, so this table only needs to have entries for modifiers that actually increase the price of the item.

UPCHARGES
---------
* SALE_ITEM_RECORD_KEY
* MODIFIER_GROUP_RECORD_KEY
* MODIFIER_RECORD_KEY