September 6, 2013 at 2:41 am
Please help on this:
I want to show the value of ISNULL(TCBOV.cboValueName, '') AS PhaseType,even if the TCBOV.cboValueIncId and TCBOV.cboValueSqlId, are not present. I have tried to replace the INNER JOIN with LEFT JOIN for the CboValues TCBOV table, but still have not worked.
SELECTS.studyCode AS studyCode,
A.activityCode AS activityCode,
ISNULL(TCBOV.cboValueName, '') AS PhaseType
FROM Activities A WITH(NOLOCK)
INNER JOIN TypesOfActivities TOA ON A.typeOfActivityIncId = TOA.typeOfActivityIncId AND A.typeOfActivitySqlId = TOA.typeOfActivitySqlId AND TOA.isDeleted=0x0
INNER JOIN ActivitiesCategories AC ON A.activityCategoryIncId = AC.activityCategoryIncId AND A.activityCategorySqlId = AC.activityCategorySqlId AND AC.isDeleted=0x0
INNER JOIN Studies S ON A.studyIncId = S.studyIncId AND A.studySqlId = S.studySqlId AND S.isDeleted=0x0
INNER JOIN TypesOfStudies TOS ON S.typeOfStudyIncId = TOS.typeOfStudyIncId AND S.typeOfStudySqlId = TOS.typeOfStudySqlId AND TOS.isDeleted=0x0
-- To Get PhaseType having the extract name “TrialFieldType”--
LEFT JOIN ActivitiesAdditionalFieldsValues AS TAFV WITH(NOLOCK) ON A.activityIncId=TAFV.activityIncId AND A.activitySqlId=TAFV.activitySqlId AND TAFV.isDeleted=0x0
INNER JOIN ActivitiesAdditionalFields AS TAF WITH(NOLOCK) ON TAFV.activityAdditionalFieldIncId=TAF.activityAdditionalFieldIncId AND TAFV.activityAdditionalFieldValueSqlId=TAF.activityAdditionalFieldSqlId AND TAF.isDeleted=0x0
AND (TAF.extractName = 'TrialFieldType')
INNER JOIN CboValues TCBOV ON TCBOV.cboValueIncId = TAFV.cboRecordIncId AND TCBOV.cboValueSqlId = TAFV.cboRecordSqlId AND TCBOV.isDeleted=0x0
September 6, 2013 at 5:28 am
Hi
I somehow managed to get it here's the code,
SELECT S.studyCode AS studyCode,
A.activityCode AS activityCode,
ISNULL(TCBOV.cboValueName, '') AS PhaseType
FROM Activities A WITH(NOLOCK)
INNER JOIN TypesOfActivities TOA ON A.typeOfActivityIncId = TOA.typeOfActivityIncId AND A.typeOfActivitySqlId = TOA.typeOfActivitySqlId AND TOA.isDeleted=0x0
INNER JOIN ActivitiesCategories AC ON A.activityCategoryIncId = AC.activityCategoryIncId AND A.activityCategorySqlId = AC.activityCategorySqlId AND AC.isDeleted=0x0
INNER JOIN Studies S ON A.studyIncId = S.studyIncId AND A.studySqlId = S.studySqlId AND S.isDeleted=0x0
INNER JOIN TypesOfStudies TOS ON S.typeOfStudyIncId = TOS.typeOfStudyIncId AND S.typeOfStudySqlId = TOS.typeOfStudySqlId AND TOS.isDeleted=0x0
-----****Here's where I changed***********----------
-- To Get PhaseType having the extract name “TrialFieldType”--
LEFT JOIN ActivitiesAdditionalFieldsValues AS TAFV WITH(NOLOCK)
INNER JOIN ActivitiesAdditionalFields AS TAF WITH(NOLOCK)
ON TAFV.activityAdditionalFieldIncId=TAF.activityAdditionalFieldIncId AND TAFV.activityAdditionalFieldValueSqlId=TAF.activityAdditionalFieldSqlId AND TAF.isDeleted=0x0
AND TAF.extractName = 'TrialFieldType'
INNER JOIN CboValues TCBOV ON TAFV.cboRecordIncId = TCBOV.cboValueIncId AND TAFV.cboRecordSqlId = TCBOV.cboValueSqlId AND TCBOV.isDeleted=0x0
ON A.activityIncId=TAFV.activityIncId AND A.activitySqlId=TAFV.activitySqlId AND TAFV.isDeleted=0x0
-----****Here's where I changed***********----------
Can anybody explain me more on that..??
September 6, 2013 at 6:09 am
This link might help.
In your first query, the left join to ActivitiesAdditionalFieldsValues AS TAFV is converted into an inner join when CboValues TCBOV is inner joined to it.
Changing the position of the two ON clauses forces SQL Server to evaluate the inner join between TCBOV and TAFV before the left join between TAFV and Activities A, and the left join is preserved.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply