question

I have two #temp tables with the same column names. I want to join the two tables but with an inner join it sends the results like this ![alt text][1] How i want the results to look: ![alt text][2] The issue with the Union is that it doesnt compare the two results. I also tried using intersect and it didnt show any results Query Used: select distinct client.CL_CASENO , p.PR_NAME , a.AD_EFFDT,a.AD_EXPDT, a.CR_CODE into #d from datamcc.dbo.MCCSADPF as sud inner join datamcc.dbo.PCHDOCPF as doc on sud.SAP_DOCID = doc.DC_RCDID inner join v_clients as client on client.CL_RCDID = doc.DCF_CLTID inner join v_Providers as p on sud.SAF_APRVID = p.PR_RCDID left outer join datamcc.dbo.CODCODPF as code on sud.SAF_TDTXTP = code.CO_RCDID inner join v_Authorizations as a on a.CL_CASENO = client.CL_CASENO where sud.SAF_APRVID in ('10118','10115','191853' )--Community program(10118),sacred heart memphis (10115)/madison(191853) and sud.SAF_TDTXTP = '16774' --Detox and CR_CODE in ('H0010') %|1985743600_1|% select cl_caseno from #d where CL_CASENO = '413938' and AD_EFFDT >= '2014-10-1' and AD_EXPDT <= GETDATE() drop table #d ------------------------------------------- --Ambulatory select distinct client.CL_CASENO , p.PR_NAME , a.AD_EFFDT , a.AD_EXPDT,a.CR_CODE into #amb from datamcc.dbo.MCCSADPF as sud inner join datamcc.dbo.PCHDOCPF as doc on sud.SAP_DOCID = doc.DC_RCDID inner join v_clients as client on client.CL_RCDID = doc.DCF_CLTID inner join v_Providers as p on sud.SAF_APRVID = p.PR_RCDID left outer join datamcc.dbo.CODCODPF as code on sud.SAF_TDTXTP = code.CO_RCDID inner join v_Authorizations as a on a.CL_CASENO = client.CL_CASENO where sud.SAF_APRVID in ('191847','10115','191853') and sud.SAF_TDTXTP in ('16778') --OutPatient and CR_CODE in('H0020') and AD_EFFDT >= '2014-10-1' and AD_EXPDT <= GETDATE() select * from #amb where CL_CASENO = '413938' drop table #amb ---------------------------------------------------------------------- Select d.cl_caseno, d.Pr_name, d.Ad_effdt from #d as d inner join #amb as a on d.CL_CASENO = a.CL_CASENO where d.AD_EFFDT <= a.AD_EFFDT [1]: /storage/temp/3503-capture.jpg [2]: /storage/temp/3504-capture2.jpg