My SQL guru is in Costa Rica for the week and have a problem I don't know how to solve. I have four simple tables and one function. For simplicity sake I'll remove any fields not required to understand what I need.
Table1:
PRIMARY_ITEM_ID,
INVENTORY_ITEM_ID
Table2:
Table2ID,
INVENTORY_ITEM_ID,
Table3ID
Table3:
Table3ID,
Department,
DepartmentJobType
Table4:
PRIMARY_ITEM_ID
DISCRETE_JOB
The function returns the JobType of the Job with Table4's DISCRETE_JOB column.
What I need: see all INVENTORY_ITEM_ID's from Table1. Only show the department when JobType from table3 is equal to the result from my function.
The links are something like..
Table1.INVENTORY_ITEM_ID -> Table2.INVENTORY_ITEM_ID
AND Table1.PRIMARY_ITEM_ID = Table4.PRIMARY_ITEM_ID
Table2.Table3ID -> Table3.Table3ID ONLY WHEN
Table3.DepartmentJobType = (SELECT FROM GetJobType_F(Table4.DISCRETE_JOB)
I'm hoping I can do this with what I have and not need to redesign my tables. (I do realize that if I had JobType in the Table1 I wouldn't need this but I would like to avoid changing the tables as much as possible. Any help would be awesome! Thanks other gurus!
I resolved the issue using a CTE query.