SQL Query - Multiple Joins

Question

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.

Don't mix explicit joins with old style comma separated joins. It's just too confusing. — jarlh
– jarlh, Commented Nov 10, 2016 at 11:54
You already are selecting these two columns. What do you really want to do? — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 10, 2016 at 11:55
I want to return all the rows from the REQ table and then left join to the REQ_TRACE table where there is a match between REQ.RQ_REQ_ID and REQ_TRACE.RT_FROM_REQ_ID. For the rows that match, I want to return the Requirement Type for both the columns R.RQ_REQ_ID and RTR.RT_TO_REQ_ID which is via the REQ_RYPE table. The second join using RTR.RT_TO_REQ_ID to the REQ table is what is confusing me. — Gray
– Gray, Commented Nov 10, 2016 at 12:14

Gordon Linoff · Accepted Answer · 2016-11-10 11:59:59Z

1

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;

answered Nov 10, 2016 at 11:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MatBailie Over a year ago

The WHERE clause currently undoes the first two LEFT JOINs, reverting them to INNER JOINs.

Gray Over a year ago

@Gordon Thanks very much. Exactly what I was looking for.

Farid Imranov · Accepted Answer · 2016-11-10 12:02:21Z

0

You need to use two join instead of writing table name after join condition with comma:

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
LEFT JOIN Req_Type RTY
ON 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

edited Nov 10, 2016 at 12:02

answered Nov 10, 2016 at 11:56

Farid Imranov

2,0952 gold badges19 silver badges32 bronze badges

2 Comments

MatBailie Over a year ago

The WHERE clause should be part of the LEFT JOIN. Otherwise you're turning this back in to INNER JOIN (because WHERE is applied after the LEFT JOIN, by which point the fields could be NULL).

Farid Imranov Over a year ago

@MatBailie, thanks for reply. I just updated my answer

Collectives™ on Stack Overflow

SQL Query - Multiple Joins

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related