Three tables are joined on the unique identifier RLI_UID from table RLI.
I would like to generate a list of RollIDs that are assigned to rows in RLAT or RLAB. Typically a
RollID is linked to RLAT or RLAB but not both. Some RollIDs are not linked to rows in either RLAT or RLAB. They should not be included in the list.
Toad for SQL Server generated most of this query (below). I manually added the OR clause to the Toad generated query (below) to eliminate rows where the RollID does not appear in either table RLAT or RLAB. Then I updated the diagram.
Toad made the WHERE clause a global where clause:
SELECT RLAT.RS_UID, RLAB.RS_UID, RLI.RollID
FROM (
GSL_PI_DEV.PISystem.RollInventory RLI
LEFT OUTER JOIN GSL_PI_DEV.PISystem.RollAssignedBottom RLAB
ON (RLI.RLI_UID = RLAB.RLI_UID))
LEFT OUTER JOIN GSL_PI_DEV.PISystem.RollAssignedTop RLAT
ON (RLI.RLI_UID = RLAT.RLI_UID)
WHERE (RLAT.RS_UID IS NOT NULL OR RLAB.RS_UID IS NOT NULL)
This is a sample of the results returned from the query:
Is there a way to model this query so the results consist of two columns which is the RS_UID from either RLAT or RLAB (wherever the RollID is found) and the RollID?
I can build a stored function that will return the results that I desire. However, I suspect that there is a way to model a query in Toad that returns the results that I desire.
Are you able to assist?