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 created 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 in Toad for SQL Server (I am using v8.1) 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 a table containing 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.
If not a Toad query model, is there a manually edited query that will return the results?
Ideally, I would like the results to be returned in two columns
RS_UID RollID
where RS_UID is either RS_UID or RS_UID1 from the query above.
Are you able to assist?
I tried modeling the query in Toad for SQL Server v8.1. I'm not proficient enough with group by and sub-queries to obtain the two column results.
If your two tables are disjoint (the ID is in RLAB or exclusively in RLAT), the most efficient way to get a resultset with both is to look for rows in one, then in the other, and UNION ALL both resultsets. The RDBMS will then be able to work efficiently, by doing each side of the UNION ALL independently (using the optimal indices for each side's tables).
SELECT
RLAB.RS_UID, RLI.RollID
FROM
GSL_PI_DEV.PISystem.RollInventory RLI
JOIN
GSL_PI_DEV.PISystem.RollAssignedBottom RLAB ON (RLI.RLI_UID = RLAB.RLI_UID)
UNION ALL
SELECT
RLAT.RS_UID, RLI.RollID
FROM
GSL_PI_DEV.PISystem.RollInventory RLI
JOIN
GSL_PI_DEV.PISystem.RollAssignedTop RLAT ON (RLI.RLI_UID = RLAT.RLI_UID)
A more concise way to write is to UNION ALL both tables before joining them to RLI, but then you probably won't get the most efficient index usage because table-specific indices will not be usable on this "merged" pseudo-table:
SELECT
RLABT.RS_UID, RLI.RollID
FROM
GSL_PI_DEV.PISystem.RollInventory RLI
JOIN
(
SELECT RLI_UID FROM GSL_PI_DEV.PISystem.RollAssignedBottom RLAB
UNION ALL
SELECT RLI_UID FROM GSL_PI_DEV.PISystem.RollAssignedTop RLAT
) RLABT ON (RLI.RLI_UID = RLABT.RLI_UID)