Gray Gray - 29 days ago 8
SQL Question

SQL Query - Multiple Joins

I'm having difficulty in joining the same table(s) twice because of which the results returned are completely incorrect.

The query below works just fine. However, I want to change it so that I can return an extra column of

Requirement Type
using the value returned in the
Requirement Traced To
column.

SELECT R.RQ_REQ_ID as "Requirement Traced From",
R.RQ_REQ_NAME as "Requirement Name",
RTY.TPR_NAME as "Requirement Type",
RTR.RT_TO_REQ_ID as "Requirement Traced To"
FROM REQ R
LEFT JOIN REQ_TRACE RTR
ON R.RQ_REQ_ID = RTR.RT_FROM_REQ_ID, Req_Type RTY
WHERE R.RQ_TYPE_ID = RTY.TPR_TYPE_ID
AND RTY.TPR_NAME in ('TOM', 'Business Process Map', 'Work Instruction', 'Functional', 'Customer Journey', 'Business')
ORDER BY 1


When I add the
REQ
and
REQ_TYPE
tables in a second time with different aliases I get hundreds of rows returned instead of the 28 I was expecting.

Any help would be appreciated.

Answer

Never use commas in the FROM clause. Always use explicit JOIN syntax.

You need to add the additional joins like this:

SELECT R.RQ_REQ_ID      as "Requirement Traced From",
       R.RQ_REQ_NAME    as "Requirement Name",
       RTY.TPR_NAME     as "Requirement Type",
       RTR.RT_TO_REQ_ID as "Requirement Traced To",
       RTY2.TPR_NAME    as "Requirement Type To",
FROM REQ R LEFT JOIN
     REQ_TRACE RTR
     ON R.RQ_REQ_ID = RTR.RT_FROM_REQ_ID LEFT JOIN
     Req_Type RTY
     ON R.RQ_TYPE_ID = RTY.TPR_TYPE_ID LEFT JOIN
     REQ R R2
     ON R2.RQ_REQ_ID = RTR.RT_TO_REQ_ID LEFT JOIN
     Req_Type RTY2
     ON RTY2.TPR_TYPE_ID = R2.RQ_TYPE_ID
WHERE RTY.TPR_NAME in ('TOM', 'Business Process Map', 'Work Instruction', 'Functional', 'Customer Journey', 'Business')
ORDER BY 1;