sql server - How can I Join Two Query -
how can join 2 queries bellow:
required output query: query#1_col_1, query#1_col_2, query#1_col3, query#2_col_1, query#2_col_2, query#2_col_3 query#01
;with rnkitems ( select dbo.tblguarantorinfo.appid, dbo.tblguarantorinfo.gname, dbo.tblguarantorinfo.grelationwithcustomer, dbo.tblguarantorinfo.gfahterspouename, dbo.tblguarantorinfo.gmothername, dbo.tblguarantorinfo.gbusinessname, dbo.tblguarantorinfo.gdesig, dbo.tblguarantorinfo.gbusinessaddressline1, dbo.tblguarantorinfo.gbusinessaddressline2, dbo.tblguarantorinfo.gbusinessaddressline3, dbo.tblguarantorinfo.residenceaddressline1, dbo.tblguarantorinfo.residenceaddressline2, dbo.tblguarantorinfo.residenceaddressline3, dbo.tblguarantorinfo.gprephone, dbo.tblguarantorinfo.gresphone, dbo.tblguarantorinfo.gmobile, dbo.tblguarantorinfo.gresstatus, dbo.tblguarantorinfo.gpermanentaddress1, dbo.tblguarantorinfo.gpermanentaddress2, dbo.tblguarantorinfo.gid, row_number() on (partition dbo.tblguarantorinfo.appid order gid, dbo.tblguarantorinfo.gname) rnk dbo.tblguarantorinfo) select appid ,min (case when rnk=1 gname end ) g1_name ,min (case when rnk=1 grelationwithcustomer end ) g1_relation ,min (case when rnk=1 gfahterspouename end ) g1_fathername ,min (case when rnk=1 gmothername end ) g1_mothername ,min (case when rnk=1 gbusinessname end ) g1_businessname ,min (case when rnk=1 gdesig end ) g1_desig ,min (case when rnk=1 gbusinessaddressline1 end ) g1_busline1 ,min (case when rnk=1 gbusinessaddressline2 end ) g1_busline2 ,min (case when rnk=1 gbusinessaddressline3 end ) g1_busline3 ,min (case when rnk=1 residenceaddressline1 end ) g1_res1 ,min (case when rnk=1 residenceaddressline2 end ) g1_res2 ,min (case when rnk=1 residenceaddressline3 end ) g1_res3 ,min (case when rnk=1 gprephone end ) g1_prephone ,min (case when rnk=1 gresphone end ) g1_resphone ,min (case when rnk=1 gmobile end ) g1_mobileno ,min (case when rnk=1 gresstatus end ) g1_resstatus ,min (case when rnk=1 gpermanentaddress1 end ) g1_peraddress1 ,min (case when rnk=1 gpermanentaddress2 end ) g1_peraddress2 ,min (case when rnk=2 gname end ) g1_name2 ,min (case when rnk=2 grelationwithcustomer end ) g2_relation ,min (case when rnk=2 gfahterspouename end ) g2_fathername ,min (case when rnk=2 gmothername end ) g2_mothername ,min (case when rnk=2 gbusinessname end ) g2_businessname ,min (case when rnk=2 gdesig end ) g2_desig ,min (case when rnk=2 gbusinessaddressline1 end ) g2_busline1 ,min (case when rnk=2 gbusinessaddressline2 end ) g2_busline2 ,min (case when rnk=2 gbusinessaddressline3 end ) g2_busline3 ,min (case when rnk=2 residenceaddressline1 end ) g2_res1 ,min (case when rnk=2 residenceaddressline2 end ) g2_res2 ,min (case when rnk=2 residenceaddressline3 end ) g2_res3 ,min (case when rnk=2 gprephone end ) g2_prephone ,min (case when rnk=2 gresphone end ) g2_resphone ,min (case when rnk=2 gmobile end ) g2_mobileno ,min (case when rnk=2 gresstatus end ) g2_resstatus ,min (case when rnk=2 gpermanentaddress1 end ) g2_peraddress1 ,min (case when rnk=2 gpermanentaddress2 end ) g2_peraddress2 ,min (case when rnk=3 gname end ) g3_name ,min (case when rnk=3 grelationwithcustomer end ) g3_relation ,min (case when rnk=3 gfahterspouename end ) g3_fathername ,min (case when rnk=3 gmothername end ) g3_mothername ,min (case when rnk=3 gbusinessname end ) g3_businessname ,min (case when rnk=3 gdesig end ) g3_desig ,min (case when rnk=3 gbusinessaddressline1 end ) g3_busline1 ,min (case when rnk=3 gbusinessaddressline2 end ) g3_busline2 ,min (case when rnk=3 gbusinessaddressline3 end ) g3_busline3 ,min (case when rnk=3 residenceaddressline1 end ) g3_res1 ,min (case when rnk=3 residenceaddressline2 end ) g3_res2 ,min (case when rnk=3 residenceaddressline3 end ) g3_res3 ,min (case when rnk=3 gprephone end ) g3_prephone ,min (case when rnk=3 gresphone end ) g3_resphone ,min (case when rnk=3 gmobile end ) g3_mobileno ,min (case when rnk=3 gresstatus end ) g3_resstatus ,min (case when rnk=3 gpermanentaddress1 end ) g3_peraddress1 ,min (case when rnk=3 gpermanentaddress2 end ) g3_peraddress2 ,min( case when rnk=4 grelationwithcustomer end ) g4_relation ,min (case when rnk=4 gname end ) g4_name ,min (case when rnk=4 gfahterspouename end ) g4_fathername ,min (case when rnk=4 gmothername end ) g4_mothername ,min (case when rnk=4 gbusinessname end ) g4_businessname ,min (case when rnk=4 gdesig end ) g4_desig ,min (case when rnk=4 gbusinessaddressline1 end ) g4_busline1 ,min (case when rnk=4 gbusinessaddressline2 end ) g4_busline2 ,min (case when rnk=4 gbusinessaddressline3 end ) g4_busline3 ,min (case when rnk=4 residenceaddressline1 end ) g4_res1 ,min (case when rnk=4 residenceaddressline2 end ) g4_res2 ,min (case when rnk=4 residenceaddressline3 end ) g4_res3 ,min (case when rnk=4 gprephone end ) g4_prephone ,min (case when rnk=4 gresphone end ) g4_resphone ,min (case when rnk=4 gmobile end ) g4_mobileno ,min (case when rnk=4 gresstatus end ) g4_resstatus ,min (case when rnk=4 gpermanentaddress1 end ) g4_peraddress1 ,min (case when rnk=4 gpermanentaddress2 end ) g4_peraddress2 rnkitems group appid query#02
select dbo.tblmasterinfo.appid , dbo.tblmasterinfo.appasignto, dbo.tblmasterinfo.appname, dbo.tblmasterinfo.apploantype, dbo.tblmasterinfo.apploanstatus, dbo.tblclientinfo.clfathername, dbo.tblclientinfo.clmothername, dbo.tblclientinfo.cldob, dbo.tblclientinfo.clprephone, dbo.tblclientinfo.clpremobile, dbo.tblclientinfo.clresidentstatus, dbo.tblclientinfo.clpreaddressline1, dbo.tblclientinfo.clpreaddressline2, dbo.tblclientinfo.clpreaddressline3, dbo.tblclientinfo.clpreaddressline4, dbo.tblclientinfo.clperaddressline1, dbo.tblclientinfo.clperaddressline2, dbo.tblclientinfo.clperaddressline3, dbo.tblclientinfo.clperaddressline4, dbo.tblprofessionalinfo.profoccupation, dbo.tblprofessionalinfo.profcompanyname, dbo.tblprofessionalinfo.profdesig, dbo.tblprofessionalinfo.profaddressline1, dbo.tblprofessionalinfo.profaddressline2, dbo.tblprofessionalinfo.profaddressline3, dbo.tblprofessionalinfo.profaddressline4 dbo.tblmasterinfo inner join dbo.tblclientinfo on dbo.tblmasterinfo.appid = dbo.tblclientinfo.appid inner join dbo.tblprofessionalinfo on dbo.tblmasterinfo.appid = dbo.tblprofessionalinfo.appid (dbo.tblmasterinfo.apploantype = 'auto loan') , (dbo.tblmasterinfo.apploanstatus = 'disbursed')
those long queries, i'm not going write in here directly, can use pattern link inner queries:
select x.column1, x.column2, y.column3, y.column4 ( -- whole query 1 ) x inner join ( -- whole query 2 ) y on x.id = y.id edit
hope works, queries big check:
;with rnkitems ( select dbo.tblguarantorinfo.appid, dbo.tblguarantorinfo.gname, dbo.tblguarantorinfo.grelationwithcustomer, dbo.tblguarantorinfo.gfahterspouename, dbo.tblguarantorinfo.gmothername, dbo.tblguarantorinfo.gbusinessname, dbo.tblguarantorinfo.gdesig, dbo.tblguarantorinfo.gbusinessaddressline1, dbo.tblguarantorinfo.gbusinessaddressline2, dbo.tblguarantorinfo.gbusinessaddressline3, dbo.tblguarantorinfo.residenceaddressline1, dbo.tblguarantorinfo.residenceaddressline2, dbo.tblguarantorinfo.residenceaddressline3, dbo.tblguarantorinfo.gprephone, dbo.tblguarantorinfo.gresphone, dbo.tblguarantorinfo.gmobile, dbo.tblguarantorinfo.gresstatus, dbo.tblguarantorinfo.gpermanentaddress1, dbo.tblguarantorinfo.gpermanentaddress2, dbo.tblguarantorinfo.gid, row_number() on (partition dbo.tblguarantorinfo.appid order gid, dbo.tblguarantorinfo.gname) rnk dbo.tblguarantorinfo) select appid ,min (case when rnk=1 gname end ) g1_name ,min (case when rnk=1 grelationwithcustomer end ) g1_relation ,min (case when rnk=1 gfahterspouename end ) g1_fathername ,min (case when rnk=1 gmothername end ) g1_mothername ,min (case when rnk=1 gbusinessname end ) g1_businessname ,min (case when rnk=1 gdesig end ) g1_desig ,min (case when rnk=1 gbusinessaddressline1 end ) g1_busline1 ,min (case when rnk=1 gbusinessaddressline2 end ) g1_busline2 ,min (case when rnk=1 gbusinessaddressline3 end ) g1_busline3 ,min (case when rnk=1 residenceaddressline1 end ) g1_res1 ,min (case when rnk=1 residenceaddressline2 end ) g1_res2 ,min (case when rnk=1 residenceaddressline3 end ) g1_res3 ,min (case when rnk=1 gprephone end ) g1_prephone ,min (case when rnk=1 gresphone end ) g1_resphone ,min (case when rnk=1 gmobile end ) g1_mobileno ,min (case when rnk=1 gresstatus end ) g1_resstatus ,min (case when rnk=1 gpermanentaddress1 end ) g1_peraddress1 ,min (case when rnk=1 gpermanentaddress2 end ) g1_peraddress2 ,min (case when rnk=2 gname end ) g1_name2 ,min (case when rnk=2 grelationwithcustomer end ) g2_relation ,min (case when rnk=2 gfahterspouename end ) g2_fathername ,min (case when rnk=2 gmothername end ) g2_mothername ,min (case when rnk=2 gbusinessname end ) g2_businessname ,min (case when rnk=2 gdesig end ) g2_desig ,min (case when rnk=2 gbusinessaddressline1 end ) g2_busline1 ,min (case when rnk=2 gbusinessaddressline2 end ) g2_busline2 ,min (case when rnk=2 gbusinessaddressline3 end ) g2_busline3 ,min (case when rnk=2 residenceaddressline1 end ) g2_res1 ,min (case when rnk=2 residenceaddressline2 end ) g2_res2 ,min (case when rnk=2 residenceaddressline3 end ) g2_res3 ,min (case when rnk=2 gprephone end ) g2_prephone ,min (case when rnk=2 gresphone end ) g2_resphone ,min (case when rnk=2 gmobile end ) g2_mobileno ,min (case when rnk=2 gresstatus end ) g2_resstatus ,min (case when rnk=2 gpermanentaddress1 end ) g2_peraddress1 ,min (case when rnk=2 gpermanentaddress2 end ) g2_peraddress2 ,min (case when rnk=3 gname end ) g3_name ,min (case when rnk=3 grelationwithcustomer end ) g3_relation ,min (case when rnk=3 gfahterspouename end ) g3_fathername ,min (case when rnk=3 gmothername end ) g3_mothername ,min (case when rnk=3 gbusinessname end ) g3_businessname ,min (case when rnk=3 gdesig end ) g3_desig ,min (case when rnk=3 gbusinessaddressline1 end ) g3_busline1 ,min (case when rnk=3 gbusinessaddressline2 end ) g3_busline2 ,min (case when rnk=3 gbusinessaddressline3 end ) g3_busline3 ,min (case when rnk=3 residenceaddressline1 end ) g3_res1 ,min (case when rnk=3 residenceaddressline2 end ) g3_res2 ,min (case when rnk=3 residenceaddressline3 end ) g3_res3 ,min (case when rnk=3 gprephone end ) g3_prephone ,min (case when rnk=3 gresphone end ) g3_resphone ,min (case when rnk=3 gmobile end ) g3_mobileno ,min (case when rnk=3 gresstatus end ) g3_resstatus ,min (case when rnk=3 gpermanentaddress1 end ) g3_peraddress1 ,min (case when rnk=3 gpermanentaddress2 end ) g3_peraddress2 ,min( case when rnk=4 grelationwithcustomer end ) g4_relation ,min (case when rnk=4 gname end ) g4_name ,min (case when rnk=4 gfahterspouename end ) g4_fathername ,min (case when rnk=4 gmothername end ) g4_mothername ,min (case when rnk=4 gbusinessname end ) g4_businessname ,min (case when rnk=4 gdesig end ) g4_desig ,min (case when rnk=4 gbusinessaddressline1 end ) g4_busline1 ,min (case when rnk=4 gbusinessaddressline2 end ) g4_busline2 ,min (case when rnk=4 gbusinessaddressline3 end ) g4_busline3 ,min (case when rnk=4 residenceaddressline1 end ) g4_res1 ,min (case when rnk=4 residenceaddressline2 end ) g4_res2 ,min (case when rnk=4 residenceaddressline3 end ) g4_res3 ,min (case when rnk=4 gprephone end ) g4_prephone ,min (case when rnk=4 gresphone end ) g4_resphone ,min (case when rnk=4 gmobile end ) g4_mobileno ,min (case when rnk=4 gresstatus end ) g4_resstatus ,min (case when rnk=4 gpermanentaddress1 end ) g4_peraddress1 ,min (case when rnk=4 gpermanentaddress2 end ) g4_peraddress2 #temp rnkitems group appid select x.*, t.* #temp t inner join (select dbo.tblmasterinfo.appid , dbo.tblmasterinfo.appasignto, dbo.tblmasterinfo.appname, dbo.tblmasterinfo.apploantype, dbo.tblmasterinfo.apploanstatus, dbo.tblclientinfo.clfathername, dbo.tblclientinfo.clmothername, dbo.tblclientinfo.cldob, dbo.tblclientinfo.clprephone, dbo.tblclientinfo.clpremobile, dbo.tblclientinfo.clresidentstatus, dbo.tblclientinfo.clpreaddressline1, dbo.tblclientinfo.clpreaddressline2, dbo.tblclientinfo.clpreaddressline3, dbo.tblclientinfo.clpreaddressline4, dbo.tblclientinfo.clperaddressline1, dbo.tblclientinfo.clperaddressline2, dbo.tblclientinfo.clperaddressline3, dbo.tblclientinfo.clperaddressline4, dbo.tblprofessionalinfo.profoccupation, dbo.tblprofessionalinfo.profcompanyname, dbo.tblprofessionalinfo.profdesig, dbo.tblprofessionalinfo.profaddressline1, dbo.tblprofessionalinfo.profaddressline2, dbo.tblprofessionalinfo.profaddressline3, dbo.tblprofessionalinfo.profaddressline4 dbo.tblmasterinfo inner join dbo.tblclientinfo on dbo.tblmasterinfo.appid = dbo.tblclientinfo.appid inner join dbo.tblprofessionalinfo on dbo.tblmasterinfo.appid = dbo.tblprofessionalinfo.appid (dbo.tblmasterinfo.apploantype = 'auto loan') , (dbo.tblmasterinfo.apploanstatus = 'disbursed')) x on x.appid = t.appid
Comments
Post a Comment