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
 Get Data from Two Tables

Author  Topic 

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2013-01-21 : 13:34:33
Hypothetical scenario to illustrate question:

TableA has two rows with two columns

Row 1:
TableA.ID (value = '123')
TableA.Name ( value = 'abc')

Row 2:
TableA.ID (value = '456')
TableA.Name ( value = 'xyz')


TableB has one row with three columns

Row 1:
TableB.ID (value = '456')
TableB.Name (value = 'xyz')
TableB.Status (value = 'passed')

I need to write SQL that will get TableA.ID, TableA.Name and TableB.Status regardless of whether TableB contains a row that corresponds to TableA or not.

In the above, TableB contains a row whose ID is 456 which corresponds to a row in TableA that has an ID of 456 ... BUT TableB does NOT contain a row with an ID of 123 that would correspond to the TableA row that has an ID of 123.

I can write SQL that returns this:

123|abc||
456|def|passed|
456|def|passed|

... the 3rd row is a duplicate of the 2nd - not correct.

I can write SQL that get this:

123|abc||
456|def||

... correct number of rows but missing TableB.Status data.

Can someone illustrate how to write SQL that returns this:

123|abc||
456|def|passed|

... this is correct.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2013-01-21 : 13:54:39
Can you show us what you have tried
Go to Top of Page

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2013-01-21 : 15:06:14
As I said, that's a hypothetical scenario. I posted the actual scenario before and got no responses. But I'll try again here.

Here's the SQL (in this *real* example, DESSTEPS is equivalent to the hypthetical TableA and STEP is equivalent to the hypothetical TableB):

SELECT
TEST.TS_TEST_ID as 'Test Plan ID',
TEST.TS_NAME as 'Test Plan Name',
TEST.TS_STEPS as 'Test Plan Step Count',
TEST.TS_RESPONSIBLE AS 'Test Plan Designer',
TEST.TS_USER_03 AS 'Test Plan Business Value',
TEST.TS_USER_02 AS 'Test Plan Complexity',
TEST.TS_USER_04 AS 'Test Plan Functional Area',
TEST.TS_USER_25 as 'Test Plan Full Folder Path',
RUN.RN_RUN_ID as 'Run ID',
RUN.RN_RUN_NAME as 'Run Name',
RUN.RN_STATUS as 'Run Status',
RUN.RN_DURATION AS "Run Duration",
RUN.RN_TESTER_NAME as 'Run Tester ID',
RUN.RN_ASSIGN_RCYC AS "Run Target Cycle",
RCY_RN.RCYC_NAME AS "Run Target Cycle Name",
TESTCYCL.TC_EXEC_DATE AS "Instance Execution Date",
TESTCYCL.TC_EXEC_TIME AS "Instance Execution Time",
TESTCYCL.TC_PLAN_SCHEDULING_DATE AS "Instance Planned Exec Date",
TESTCYCL.TC_PLAN_SCHEDULING_TIME AS "Instance Planned Exec Time",
TESTCYCL.TC_ASSIGN_RCYC as 'Instance Assigned Cycle',
RCY_TC.RCYC_NAME AS "Instance Cycle Name",
TESTCYCL.TC_USER_01 as 'Instance Responsible Tester Name',
TESTCYCL.TC_USER_02 as 'Instance Planned End Date',
CYCLE.CY_CYCLE AS "Test Set Name",
CYCLE.CY_CYCLE_ID AS "Test Set ID",
CYCLE.CY_USER_01 AS "Test Set Functional Area",
CONVERT(NVARCHAR(MAX),CYCLE.CY_COMMENT) AS "Test Set Description", --QC Memo fields must be converted to nvarchar
CYCLE.CY_ASSIGN_RCYC AS "Test Set Target Cycle ID" ,
RCY_CY.RCYC_NAME AS "Test Set Target Cycle Name",
CONVERT(NVARCHAR(MAX),CYCLE.CY_USER_07) AS "Test Set Full Folder Path", --QC Memo fields must be converted to nvarchar
DESSTEPS.DS_STEP_NAME as 'Step Name',
STEP.ST_STATUS as 'Step Status' ,
DESSTEPS.DS_ID as 'Step ID',
STEP.ST_EXECUTION_DATE as 'Step Execution Start Date',
DESSTEPS.DS_DESCRIPTION as 'Step Description',
DESSTEPS.DS_USER_01 as 'Step Test Data',
DESSTEPS.DS_USER_02 AS 'Step Audit Evidence(DESC)',
DESSTEPS.DS_USER_03 as 'Step Audit Evidence Required',
DESSTEPS.DS_USER_04 AS 'Step Audit Verification Type',
DESSTEPS.DS_USER_05 as 'Step Audit Approve/Reject',
DESSTEPS.DS_USER_06 as 'Step Control Point ID(s)',
STEP.ST_EXECUTION_TIME as 'Step Execution Time',
STEP.ST_ACTUAL as 'Step Actual Result'
FROM CYCL_FOLD LEFT JOIN CYCLE ON CYCL_FOLD.CF_ITEM_ID = CYCLE.CY_FOLDER_ID
LEFT JOIN TESTCYCL ON CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID
LEFT JOIN TEST ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
LEFT JOIN RUN ON TEST.TS_TEST_ID = RUN.RN_TEST_ID AND RN_EXECUTION_DATE = (SELECT MAX(RN_EXECUTION_DATE) FROM RUN WHERE TC_TESTCYCL_ID = RN_TESTCYCL_ID) AND RN_EXECUTION_TIME = (SELECT MAX(RN_EXECUTION_TIME) FROM RUN WHERE TC_TESTCYCL_ID = RN_TESTCYCL_ID AND RN_EXECUTION_DATE = (SELECT MAX(RN_EXECUTION_DATE) FROM RUN WHERE TC_TESTCYCL_ID = RN_TESTCYCL_ID))
LEFT JOIN RELEASE_CYCLES RCY_RN ON RCY_RN.RCYC_ID = RN_ASSIGN_RCYC
LEFT JOIN RELEASE_CYCLES RCY_CY ON RCY_CY.RCYC_ID = CY_ASSIGN_RCYC
LEFT JOIN RELEASE_CYCLES RCY_TC ON RCY_TC.RCYC_ID = TC_ASSIGN_RCYC
LEFT JOIN STEP ON RUN.RN_RUN_ID = STEP.ST_RUN_ID
LEFT JOIN DESSTEPS ON DESSTEPS.DS_TEST_ID = TEST.TS_TEST_ID --AND DESSTEPS.DS_ID = STEP.ST_DESSTEP_ID
WHERE TESTCYCL.TC_ASSIGN_RCYC = @Assigned_Cycle@ AND TS_TYPE = 'MANUAL'
ORDER BY TEST.TS_TEST_ID

This is the scenario where the rows are duplicated (like in the hypothetical scenario, the row from TableB is duplicated).
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-21 : 15:33:48
What are the queries that you tried based on the hypothetical scenario?
I am curious to see the query that produced the two rows with ID = 456.
Seems to me that the result you need can be obtained with a LEFT JOIN as shown below
SELECT
a.Id, a.value,
b.status
FROM
TableA a
LEFT JOIN TableB b ON
a.id = b.Id AND a.value = b.value;
The real example, as you surmised, is too complex for anyone to understand unless there is readily consumable data to test it.
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2013-01-21 : 15:35:00
Based on example this should work but on actual case you have to give more explanation

Select distinct T1.ID,T1.Name,ISNULL(T2.Status,'') Status
from TableA T1
Left join TableB T2 on T2.ID = T1.ID and T2.Name = T1.Name
Go to Top of Page

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2013-01-21 : 16:53:12
Ok, let me try again from a different angle. I've stripped everything from that LONG piece of SQL that wasn't necessary to show the problem.

Here it is now:


SELECT
DESSTEPS.DS_STEP_NAME as 'Design Step Name',
STEP.ST_ID AS 'Step ID',
STEP.ST_STATUS as 'Step Status'
FROM CYCLE
LEFT JOIN TESTCYCL ON CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID
LEFT JOIN TEST ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
LEFT JOIN RUN ON TEST.TS_TEST_ID = RUN.RN_TEST_ID AND RN_EXECUTION_DATE = (SELECT MAX(RN_EXECUTION_DATE) FROM RUN WHERE TC_TESTCYCL_ID = RN_TESTCYCL_ID) AND RN_EXECUTION_TIME = (SELECT MAX(RN_EXECUTION_TIME) FROM RUN WHERE TC_TESTCYCL_ID = RN_TESTCYCL_ID AND RN_EXECUTION_DATE = (SELECT MAX(RN_EXECUTION_DATE) FROM RUN WHERE TC_TESTCYCL_ID = RN_TESTCYCL_ID))
LEFT JOIN STEP ON STEP.ST_RUN_ID = RUN.RN_RUN_ID
LEFT JOIN DESSTEPS ON DESSTEPS.DS_TEST_ID = TEST.TS_TEST_ID -- AND DESSTEPS.DS_ID = STEP.ST_DESSTEP_ID

WHERE TESTCYCL.TC_ASSIGN_RCYC = 1040


Here's the result set:

Design Step Name........Step ID.........Step Status
Step 1
Step 2
Step 1............................23775...........Passed
Step 2............................23775...........Passed
Step 1............................23776...........Failed
Step 2............................23776...........Failed
Step 1
Step 2

All is correct EXCEPT row 5 and row 6 should not be there. The output should be:

Design Step Name........Step ID.........Step Status
Step 1
Step 2
Step 1............................23775...........Passed
Step 2............................23776...........Failed
Step 1
Step 2

The reason is, the problem steps - the ones that have a Step ID and Step Status - apparently the SQL picks them twice because data is found in BOTH tables. That is, Design Step Name is in the DESSTEPS table and Step ID and Step status are in the STEP table.
Go to Top of Page
   

- Advertisement -