Query assistance

[wfairban]

I am needing some assistance with a query. I would like to pull all requirements from the td.req and td.req_cover table that do not have test cases associated with them Here is my query. I would like for it to do the exact opposite.

Select RQ_REQ_NAME,RQ_USER_07,RQ_USER_11,TS_NAME,TS_STEPS
from td.REQ,td.test,td.REQ_COVER where RQ_REQ_PATH like 'ACL%'
AND RC_REQ_ID=RQ_REQ_ID
AND RC_TEST_ID=TS_TEST_ID
order by RQ_USER_07

***I want all instances of the requirement name, etc that does not have a test associated with it. If you need more information please let me know. I have tried a subquery and NOT EXISTS and no luck. Thanks in advance!
Newbie at this!

 

[gmmastros]

I'm sure we can help. Could you please post some sample data with expected results so that we can help you better.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[wfairban]

George,

I would like to see the results being all requirements without Test Cases associated with them. For example,
I have a table called requirements with field B. I have a table called test with test case (ts_name). For every requirement there is not always a test ran on it. I would like to pull the requirements that have not yet had a test case associated with it.
I am not sure how to put the sample data up since this is a relational db and the data is proprietary. Any suggestions to a solution.
Thanks so much for your help!

 

[gmmastros]

I understand about proprietary databases. The question is a little confusing to me. Feel free to change table names, field names, and data values. If you want, just show integers in your field data. There's certainly nothing proprietary about integers.

In order to help you better, It's important to know which fields from 1 table are linked to fields in the other tables.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jbenson001]

Just create some dummy data to show us for the necessary tables.

 

[wfairban]

Ok here it goes.
REQ -- table
RQ_REQ_ID (PRIKEY, INT) 1, 2, 3
RQ_REQ_NAME -FR002, FR003, FR004

REQ_COVER -- table
RC_REQ_ID (PRIKEY, INT) 1, 2, 3
RC_TEST_ID (PRIKEY, INT) 1,2,3
RC_STEP_ID (INT, NULL)
RC_ORDER_ID (INT, NULL)

Test --table
TS_TEST_ID (PRIKEY, INT)1,2,3
TS_NAME (VARCHAR, 255) Scripts Remove leading .....

I hope this helps! Let me know what else you need.
thanks,

 

[gmmastros]

I'm not sure how the REQ_COVER table fits in to this, so, for now, I will ignore it. The code I show below will create table variable to store some dummy data. You should be able to copy/paste this in to Query Analyzer and run it to see if it does what you want.

[green]-- Setting up some dummy data[/green]
Declare @REQ Table(RQ_REQ_ID Integer, RQ_REQ_NAME VarChar(100))

Insert Into @REQ Values(1, 'FR002')
Insert Into @REQ Values(2, 'FR003')
Insert Into @REQ Values(3, 'FR004')

Declare @Test Table(TS_TEST_ID INT, TS_NAME VarChar(255))

Insert Into @Test Values(1, 'Scripts Remove Leading...')

[green]-- The query[/green]
Select R.RQ_REQ_ID,
       R.RQ_REQ_NAME
From   @REQ R 
       Left Join @Test T
         On T.TS_TEST_ID = R.RQ_REQ_ID
Where  T.TS_TEST_ID Is NULL

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[wfairban]

I think you are understanding what I am saying now but I don't want to insert anything into the table. I would like to determine if there is anything in the test table. Once a requirement has been created a test can be run on it. I would like to see which requirements have not had a test run on them. The req_cover table simply links the three together with the IDs. I hope I have not totally confused you. LOL

Your help is very appreciated. If I get this to work you will get 5 stars!!!!!

 

[wfairban]

Sorry I did not see the rest of the query. I am trying it now.