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
 Why is LEFT JOIN Not Working?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

planetoneautomation
Yak Posting Veteran

97 Posts

Posted - 12/04/2012 :  12:29:04  Show Profile  Reply with Quote
I thought I understood that a LEFT JOIN would return all rows from the left table regardless of the existence of matching records in the right table.

In the following SQL, I'm only getting rows where there is a record in the RUN table, even though I'm using LEFT JOIN.

Where am I going wrong? I want to return data even if there is no row in the RUN table.


-- Test Execution

SELECT

RUN.RN_RUN_NAME AS "Run Name",
RUN.RN_RUN_ID AS "Run ID",
RUN.RN_EXECUTION_DATE AS "Run Execution Date",
RUN.RN_EXECUTION_TIME AS "RunExecution Time",
RUN.RN_DURATION AS "Run Duration",
RUN.RN_STATUS AS "Run Status",
RUN.RN_ASSIGN_RCYC AS "Run Target Cycle",
RCY_RN.RCYC_NAME AS "Run Target Cycle Name",
RUN.RN_TESTER_NAME AS "Run Tester Name",

TESTCYCL.TC_ACTUAL_TESTER AS "Instance Actual Tester",
TESTCYCL.TC_PINNED_BASELINE AS "Instance Baseline",
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_TESTER_NAME AS "Instance Responsible Tester",
TESTCYCL.TC_STATUS AS "Instance Status",
TESTCYCL.TC_ASSIGN_RCYC AS "Instance Target Cycle",
RCY_TC.RCYC_NAME AS "Instance Target Cycle Name",
TESTCYCL.TC_USER_01 AS "Instance Responsible Tester Name",

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

TEST.TS_RESPONSIBLE AS "Plan Designer",
TEST.TS_USER_03 AS "Plan Business Value",
TEST.TS_USER_02 AS "Plan Complexity",
TEST.TS_USER_04 AS "Plan Functional Area"

FROM TESTCYCL

-- this restricts counts to last run execution date/time
LEFT JOIN
(
  SELECT
  RN_RUN_ID, RN_RUN_NAME, RN_EXECUTION_DATE, RN_EXECUTION_TIME, RN_DURATION, RN_STATUS,
  RN_ASSIGN_RCYC, RN_TESTER_NAME, RN_TESTCYCL_ID, ROW_NUMBER()
  OVER(PARTITION BY RN_TESTCYCL_ID ORDER BY RN_EXECUTION_DATE DESC,RN_EXECUTION_TIME DESC) AS LASTRUN
  FROM RUN
) RUN ON TC_TESTCYCL_ID = RN_TESTCYCL_ID

LEFT JOIN TEST ON TS_TEST_ID = TESTCYCL.TC_TEST_ID
LEFT JOIN CYCLE ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
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 RELEASE_CYCLES RCY_RN ON RCY_RN.RCYC_ID = RN_ASSIGN_RCYC

-- this gets the last run execution
WHERE RUN.LASTRUN = 1 AND ISNULL(CAST(RCY_RN.RCYC_NAME as nvarchar(100)),'') LIKE  @Run_Release_Cycle_Name@

ORDER BY RUN.RN_RUN_ID

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/04/2012 :  12:30:34  Show Profile  Reply with Quote
Instead of Where ...put AND
Go to Top of Page

planetoneautomation
Yak Posting Veteran

97 Posts

Posted - 12/04/2012 :  13:25:36  Show Profile  Reply with Quote
Wow. I thought the issue was more complicated - turned out to be very simple. Thank you.
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