SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Left Join Problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

planetoneautomation
Yak Posting Veteran

98 Posts

Posted - 11/06/2012 :  07:12:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 11/06/2012 :  07:20:24  Show Profile  Reply with Quote
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
Go to Top of Page

planetoneautomation
Yak Posting Veteran

98 Posts

Posted - 11/06/2012 :  07:45:06  Show Profile  Reply with Quote
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.
Go to Top of Page

planetoneautomation
Yak Posting Veteran

98 Posts

Posted - 11/06/2012 :  08:12:19  Show Profile  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 11/06/2012 :  08:19:08  Show Profile  Reply with Quote
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

Edited by - bandi on 11/06/2012 08:20:08
Go to Top of Page

planetoneautomation
Yak Posting Veteran

98 Posts

Posted - 11/06/2012 :  08:36:07  Show Profile  Reply with Quote
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!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000