Adding Column While Selecting Table in SQl
- by kmkperumal
My First Table is ProjectCustomFields
CustomFieldId ProjectId CustomFieldName CustomFieldRequired CustomFieldDataType
69 1 User Name 1 0
72 1 City 1 0
74 1 Email 0 0
82 1 Salary 1 2
My Second Table is ProjectCustomFieldValues
CustomFieldValueId ProjectId CustomFieldId CustomFieldValue RecordId
35 1 69 kaliya 1
36 1 72 Bangalore 1
37 1 74 [email protected] 1
41 1 69 Yohesh 2
42 1 72 Delhi 2
43 1 74 2
50 1 69 sss 3
51 1 72 Delhi 3
52 1 74 [email protected] 3
57 1 69 Sunil 4
58 1 72 Mumbai 4
59 1 74 [email protected] 4
60 1 82 20000 4
I tried Below Query
Select M.CustomFieldName,N.CustomFieldValue,N.RecordId From
(Select G.CustomFieldName,H.RecordId From
(Select CustomFieldName From ProjectCustomFields Where ProjectId=1) G Cross Join
(Select Distinct RecordId From ProjectCustomFieldValues) H) M
Left Join
(Select CustFiled.CustomFieldName,CustValue.CustomFieldValue,CustValue.RecordId From ProjectCustomFieldValues CustValue Left Join
ProjectCustomFields CustFiled On CustValue.CustomFieldId=CustFiled.CustomFieldId Where CustValue.AuctionId=1
) N On M.CustomFieldName=N.CustomFieldName And M.RecordId=N.RecordId
But I got the result below
#CustomFieldName# CustomFieldValue RecordId
User Name kaliya 1
City Bangalore 1
Email [email protected] 1
Salary NULL **NULL**
User Name Yohesh 2
City Delhi 2
Email 2
Salary NULL **NULL**
User Name sss 3
City Delhi 3
Email [email protected] 3
Salary NULL **NULL**
User Name NULL **NULL**
City NULL **NULL**
Email NULL **NULL**
Salary NULL **NULL**
User Name Sunil 4
City Mumbai 4
Email [email protected] 4
Salary 20000 4
But Expected Result is
CustomFieldName CustomFieldValue RecordId
User Name kaliya 1
City Bangalore 1
Email [email protected] 1
Salary NULL **1**
User Name Yohesh 2
City Delhi 2
Email 2
Salary NULL **2**
User Name sss 3
City Delhi 3
Email [email protected] 3
Salary NULL **3**
User Name Sunil 4
City Mumbai 4
Email [email protected] 4
Salary 20000 4
Please guide me some one,I tried so much but i got null value in recordId,So I need same recordId above one..
