Click here to Skip to main content
15,064,026 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SELECT R.*,A.* FROM [RECRUIT] AS R LEFT JOIN [AUTHORIZATION] A ON R.FORM_ID=A.FORM_ID


please help me by writing the code for left join with the above code.
I also need to left join the second table [CONFIRMATION]

select [FORM_ID],[Decision] from [CONFIRMATION]
Posted

Should be as simple as below.

SELECT R.*, A.*, C.*
FROM [RECRUIT] AS R
LEFT JOIN [AUTHORIZATION] A
ON R.FORM_ID = A.FORM_ID
LEFT JOIN [CONFIRMATION] C
ON R.FORM_ID = C.FORM_ID
   
Just add at the end of your current query the following:

SQL
SELECT R.*,A.*,C.*
FROM [RECRUIT] AS R 
LEFT JOIN [AUTHORIZATION] A ON R.FORM_ID=A.FORM_ID
LEFT JOIN [CONFIRMATION] C ON R.FORM_ID = C.FORM_ID




However, I don't know if you want the join criteria to be with R (Recruit) or with A (Authorization).
Look for the best scenario below:



if you do...
SQL
... LEFT JOIN [CONFIRMATION] C ON R.FORM_ID = C.FORM_ID

... then you'll get C if it exists in R, even when A doen't exist



if you do...
SQL
... LEFT JOIN [CONFIRMATION] C ON A.FORM_ID = C.FORM_ID

... then you'll get C if it exists in A, ONLY when A exists too.



Finally, if you need C if and only if both R and A exist, then you should use...
SQL
... LEFT JOIN [CONFIRMATION] C ON R.FORM_ID = C.FORM_ID AND A.FORM_ID = C.FORM_ID
   
v2
Comments
sathish kumar 10-Oct-13 11:51am
   
Thanks guys,
It's really useful

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900