Left Join Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Left Join Problem

Author

Topic

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-11-06 : 07:12:16

Can't figure out why the LEFT JOIN gives error "The multi-part identifier 'TEST.TS_TEST_ID' could not be bound". I've used LEFT JOIN in similar SQL without a problem.

A row in the CYCLE table references one or more rows in the TEST table. Each TEST table row may or may not have a reference to the REQ_COVER table. I'm trying to find those TEST table rows (referenced by the CYCLE table) that have no reference in the REQ_COVER table.

What am I doing wrong?



-- Test Sets with Tests Not Covering a Requirement

SELECT

  CYCLE.CY_CYCLE_ID AS "Test Set ID",
  CYCLE.CY_CYCLE AS "Test Set Name",
  CYCL_FOLD.CF_ITEM_NAME AS "Test Set Folder",
  TEST.TS_TEST_ID AS "Test ID",
  TEST.TS_NAME AS "Test Name",
  TEST.TS_USER_04 AS "Test Functional Area",
  REQ.RQ_REQ_ID AS "Req ID",
  REQ.RQ_REQ_NAME AS "Req Name"

FROM REQ, TEST, REQ_COVER, TESTCYCL, CYCLE, CYCL_FOLD

LEFT JOIN REQ_COVER ON TEST.TS_TEST_ID = REQ_COVER.RC_ENTITY_ID

WHERE

  REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
  AND TEST.TS_TEST_ID = REQ_COVER.RC_ENTITY_ID
  AND TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
  AND TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
  AND CYCLE.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID
  AND REQ.RQ_REQ_ID IS NULL

ORDER BY CYCLE.CY_CYCLE_ID

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-06 : 07:20:24

SELECT

CYCLE.CY_CYCLE_ID AS "Test Set ID",
CYCLE.CY_CYCLE AS "Test Set Name",
CYCL_FOLD.CF_ITEM_NAME AS "Test Set Folder",
TEST.TS_TEST_ID AS "Test ID",
TEST.TS_NAME AS "Test Name",
TEST.TS_USER_04 AS "Test Functional Area",
REQ.RQ_REQ_ID AS "Req ID",
REQ.RQ_REQ_NAME AS "Req Name"

FROM REQ
JOIN REQ_COVER ON REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
JOIN TEST ON TEST.TS_TEST_ID = REQ_COVER.RC_ENTITY_ID
JOIN TESTCYCL ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
JOIN CYCLE ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
JOIN CYCL_FOLD ON CYCLE.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID
WHERE REQ.RQ_REQ_ID IS NULL
ORDER BY CYCLE.CY_CYCLE_ID

--
Chandu

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-11-06 : 07:45:06

Thanks. That runs without error.

Unfortunately, it returns no rows and there are definitely rows that meet the criteria I'm trying to select on. I have setup this condition through the application and can see the data through the application.

Let me get real specific in English about the criteria ...

1. CYCLE table: holds name and key (ID) for something called a "Test Set"

2. A "Test Set" contains one or more instances of a "Test". The TESTCYCL table holds a key (ID) to the CYCLE table as well as a key (ID) to a row in the TEST table. There will be one or more rows in the TESTCYCL table for any "Test Set". Thus, a "Test Set" contains one or more "Tests".

3. The REQ_COVER table is an associative table and links something called a "Requirement" to a "Test". The REQ_COVER table has a column RC_ENTITY_ID that contains the key (ID) to a "Test" in the TEST table. There MAY or MAY NOT be a row in the REQ_COVER table for a "Test" in the TEST table.

4. I'm trying to find all "Tests" that are contained in a "Test Set" but which have no row in the REQ_COVER table.

Hope this helps explain a little better. I'm just lost right now with JOINS. I can write simple code (VB or C## for example) to do simpler SQL and analyze the results and perform more SQL to get what I want. But in this instance, I really must do it in a single SQL statement.

Any help is greatly appreciated.

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-11-06 : 08:12:19

Let me put this another way - maybe this will help ...

The following SQL selects the *opposite* of what I want to do with the SQL in my original post:



SELECT

  CYCLE.CY_CYCLE_ID AS "Test Set ID",
  CYCLE.CY_CYCLE AS "Test Set Name",
  CYCL_FOLD.CF_ITEM_NAME AS "Test Set Folder",
  TEST.TS_USER_04 AS "Test Functional Area",
  TEST.TS_TEST_ID AS "Test ID",
  TEST.TS_NAME AS "Test Name",
  REQ.RQ_REQ_ID AS "Req ID",
  REQ.RQ_REQ_NAME AS "Req Name"

FROM REQ, REQ_COVER, TEST, TESTCYCL, CYCLE, CYCL_FOLD

WHERE

  REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
  AND TEST.TS_TEST_ID = REQ_COVER.RC_ENTITY_ID
  AND TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
  AND TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
  AND CYCLE.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID
  AND REQ.RQ_REQ_ID IS NOT NULL

ORDER BY CYCLE.CY_CYCLE_ID

This returns a row for every Test in a Test Set that has a linked Requirement.

I originally thought taking the "NOT" out of the last statement in the WHERE clause would do it:



  AND REQ.RQ_REQ_ID IS NULL

... but this returns no rows. I think the SQL Chandu posted for me does essentially the same thing as what I just described with taking the "NOT" out.

I tried using LEFT JOIN (see the SQL in my original post) but that was giving me an error.

Still at a loss.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-06 : 08:19:08

Try this,

FROM REQ
JOIN REQ_COVER ON REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
RIGHT JOIN TEST ON TEST.TS_TEST_ID = REQ_COVER.RC_ENTITY_ID
JOIN TESTCYCL ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
JOIN CYCLE ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
JOIN CYCL_FOLD ON CYCLE.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID
WHERE REQ.RQ_REQ_ID IS NOT NULL
ORDER BY CYCLE.CY_CYCLE_ID

--
Chandu

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-11-06 : 08:36:07

Perfect except ...

WHERE REQ.RQ_REQ_ID IS NOT NULL

... should be:

WHERE REQ.RQ_REQ_ID IS NULL

It was that I needed the RIGHT, not the LEFT JOIN. One of these days, I'll get the JOIN down.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources