Combine Two table datas

Question

I have two table A and B, i need a report by combining these two tables and the output should be as below image. How can i achieve it. Please help me out.

What have you tried so far? SO can help you with errors, but isn't a code writing service. — David Rushton, Jan 28 '16 at 16:04
Do you really want separate columns in the output for the two that have the same name? If you had a row in both tables with the same values in *both* of those columns would you still want two rows in your output; or one row with both `Steps__c` and `Name__c` populated? You need to think through exactly what you need and see how far you can get. — Alex Poole, Jan 28 '16 at 16:05
Table A select A.INFRASTRUCTURE_CHANGE_ID,B.name__c,b.Support_group from LCL_CRQ_ImplementationPlan_col A right outer join LCL_CRQ_ImplementationSect3998 "B" on A.INFRASTRUCTURE_CHANGE_ID=B.Infrastructure_Change_ID where A.INFRASTRUCTURE_CHANGE_ID='CRQ000001192211'; Table B select A.INFRASTRUCTURE_CHANGE_ID,B.Steps__c,b.Support_group from LCL_CRQ_ImplementationPlan_col A right outer join LCL_CRQ_ImplementationSect4000 "B" on A.INFRASTRUCTURE_CHANGE_ID=B.Infrastructure_Change_ID__c where A.INFRASTRUCTURE_CHANGE_ID='CRQ000001192211'; — Subbu, Jan 28 '16 at 17:03
Combination of Table A and Table B, i tried this, but its not what i want select * from Table A full outer join Table B on ImpAct.INFRASTRUCTURE_CHANGE_ID=Participants.INFRASTRUCTURE_CHANGE_ID where ImpAct.INFRASTRUCTURE_CHANGE_ID='CRQ000001192211'; — Subbu, Jan 28 '16 at 17:07
@ Alex: yes, i want it in the same fashion as i have mentioned in the image above — Subbu, Jan 28 '16 at 17:10

score 1 · Accepted Answer · edited May 23 '17 at 10:27

I think the following could work with a union all. It will do a full table scan on both tables which could be a matter of concern if these tables are having huge amounts of data. Please check the query plan first.

select A_INFRASTRUCTURE_CHANGE_ID, A_Name_c, A_Support_group, B_INFRASTRUCTURE_CHANGE_ID, B_Steps_c, B_Support_group
FROM
    (SELECT  INFRASTRUCTURE_CHANGE_ID as A_INFRASTRUCTURE_CHANGE_ID, Name_c as A_Name_c, 
         Support_group as A_Support_group, NULL as B_INFRASTRUCTURE_CHANGE_ID , NULL as B_Steps_c, NULL  as B_Support_group 
FROM TABLE_A
UNION ALL 
 SELECT NULL as A_INFRASTRUCTURE_CHANGE_ID,NULL as A_Name_c,NULL as A_Support_group ,
        INFRASTRUCTURE_CHANGE_ID as B_INFRASTRUCTURE_CHANGE_ID, Steps_c as B_Steps_c, Support_group as B_Support_group 
FROM TABLE_B);

Sabiha

Combine Two table datas

1 Answers1