I have created the below select query that joins 5 SQL tables. The modifier table either uses the NAME column to display the modifier name or the ITEM_RECORD_KEY column to reference the RECORD_KEY of the ITEM row from the ITEM table to display the Modifier name.
My query only returns the modifier name if it does not references the ITEM_RECORD_KEY. I need to return either the MODIFIER.NAME or the ITEM.NAME in the MODIFIER column of my query result.
Query:
SELECT
I.NAME AS 'ITEM NAME',
ML.NAME AS 'MODIFIER LIST',
MG.NAME AS 'MODIFIER GROUP',
M.NAME AS 'MODIFIER',
FORMAT(CAST(M.UPCHARGE_EXPRESSION AS numeric), 'c', 'en-us') AS 'UPCHARGE'
FROM
ITEM
RIGHT JOIN
[dbo].[ITEM] I ON I.RECORD_KEY = I.RECORD_KEY
RIGHT JOIN
[dbo].[ITEM_MODIFIER] IM ON IM.ITEM_RECORD_KEY = I.RECORD_KEY
RIGHT JOIN
[dbo].[MODIFIER_LIST] ML ON ML.RECORD_KEY = IM.MODIFIER_LIST_RECORD_KEY
RIGHT JOIN
[dbo].[MODIFIER_GROUP] MG ON MG.MODIFIER_LIST_RECORD_KEY = ML.RECORD_KEY
RIGHT JOIN
[dbo].[MODIFIER] M ON M.MODIFIER_GROUP_RECORD_KEY = MG.RECORD_KEY
GROUP BY
I.NAME, ML.NAME, MG.NAME, M.NAME, M.UPCHARGE_EXPRESSION
ORDER BY
I.NAME, ML.NAME, MG.NAME, M.NAME
This is the current results from that query:
| ITEM NAME | MODIFIER LIST | MODIFIER GROUP | MODIFIER | UPCHARGE |
|---|---|---|---|---|
| $2 sundae sale | ICE CREAM FLAVORS | CHOOSE ICE CREAM | NULL | NULL |
| $2 sundae sale | ICE CREAM FLAVORS | CHOOSE ICE CREAM | CHOOSE LATER | NULL |
[Here is proof that the above query runs]
(https://i.sstatic.net/7o1QDkbe.png)
This is the expected results I am trying to get from my query:
| ITEM NAME | MODIFIER LIST | MODIFIER GROUP | MODIFIER | UPCHARGE |
|---|---|---|---|---|
| $2 sundae sale | ICE CREAM FLAVORS | CHOOSE ICE CREAM | BLACK RASP IC | NULL |
| $2 sundae sale | ICE CREAM FLAVORS | CHOOSE ICE CREAM | CHOC CHIP IC | NULL |
| $2 sundae sale | ICE CREAM FLAVORS | CHOOSE ICE CREAM | CHOC CHOC CHIP IC | NULL |
| $2 sundae sale | ICE CREAM FLAVORS | CHOOSE ICE CREAM | CHOOSE LATER | NULL |
| $2 sundae sale | ICE CREAM FLAVORS | CHOOSE ICE CREAM | COFFEE IC | NULL |
| $2 sundae sale | ICE CREAM FLAVORS | CHOOSE ICE CREAM | COOKIE DOUGH IC | NULL |
| $2 sundae sale | ICE CREAM FLAVORS | CHOOSE ICE CREAM | STRAWBERRY IC | NULL |
| $2 sundae sale | ICE CREAM FLAVORS | CHOOSE ICE CREAM | VANILLA IC | NULL |
| $2 sundae sale | ICE CREAM FLAVORS | CHOOSE ICE CREAM | VEGAN OREO | NULL |
Here are the tables to reproduce the database:
[ITEM] table (data types bigint and nvarchar(128)):
| RECORD_KEY | NAME |
|---|---|
| 5935753887661641498 | COOKIE DOUGH IC |
| 5935753858146389769 | BLACK RASP IC |
| 5935753875199381267 | CHOC CHIP IC |
| 5935753885692612377 | COFFEE IC |
| 5935753956554460991 | STRAWBERRY IC |
| 5935753967705804613 | VANILLA IC |
| 5935753973608501064 | VEGAN OREO |
| 5935753877166837524 | CHOC CHOC CHIP IC |
| 3189656780758021441 | $2 sundae sale |
[MODIFIER_LIST] table (datatypes bigint and nvarchar(128))
| RECORD_KEY | NAME |
|---|---|
| 5992426424268357750 | ICE CREAM FLAVORS |
[MODIFIER_GROUP] table (datatypes bigint, bigint and nvarchar(128)):
| RECORD_KEY | MODIFIER_LIST_RECORD_KEY | NAME |
|---|---|---|
| 5992441193994846327 | 5992426424268357750 | CHOOSE ICE CREAM |
[ITEM_MODIFIER] table (datatypes bigint, bigint and bigint):
| RECORD_KEY | ITEM_RECORD_KEY | MODIFIER_LIST_RECORD_KEY |
|---|---|---|
| 3189656801236120901 | 3189656780758021441 | 5992426424268357750 |
[MODIFIER] table (datatypes bigint, bigint, nvarchar(128), bigint and nvarchar(64)):
| RECORD_KEY | MODIFIER_GROUP_RECORD_KEY | NAME | ITEM_RECORD_KEY | UPCHARGE_EXPRESSION |
|---|---|---|---|---|
| 5992479917748650109 | 5992441193994846327 | NULL | 5935753858146389769 | $0.00 |
| 5992548895904366727 | 5992441193994846327 | NULL | 5935753875199381267 | $0.00 |
| 5992553113958285448 | 5992441193994846327 | NULL | 5935753877166837524 | $0.00 |
| 5992590751191728269 | 5992441193994846327 | NULL | 5935753885692612377 | $0.00 |
| 5992596587928879246 | 5992441193994846327 | NULL | 5935753887661641498 | $0.00 |
| 5992752244786331827 | 5992441193994846327 | NULL | 5935753956554460991 | $0.00 |
| 5992774327866622137 | 5992441193994846327 | NULL | 5935753967705804613 | $0.00 |
| 5992784415143821500 | 5992441193994846327 | NULL | 5935753973608501064 | $0.00 |
| 5993568288307740930 | 5992441193994846327 | CHOOSE LATER | NULL | 0.00 |
I believe I need to add a second select statement for the MODIFIER table to pull the items names using the ITEM_RECORD_KEY and ITEM.NAME, but I am not sure how to do this or if this is even the correct thing to do.
Any help would be greatly appreciated
Joseph Laurello is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
5
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
2