i using sql server 2008 , trying join data 4 different tables tables related such 2nd table child of 1st , 3rd & 4th tables both children of 2nd, using following statement output results rather getting 9 distinct records (5 cr table , 4 cx table) getting 20 records data 3rd & 4th tables duplicated.
if omit references table cx desired 5 results , omitting references cr gives desired 4 results, require 9 results both tables combined returned instead of 20 records get. post screenshots unable due reputation sorry.
select ls.sitecode, ep.ep_key, c0.leaseid, c0.sdate leasestart, c0.edate leaseexpiry, cr.effdate renewaldate, cx.sdate reviewdate ls inner join fmep ep on ls.sitecode = ep.sitecode inner join c0 on ep.ep_key = c0.ep_key inner join cr on c0.leaseid = cr.leaseid inner join cx on c0.leaseid = cx.leaseid ls.sitecode = 2121 i have searched around last couple of hours solution i'm not using correct search terms due lack of familiarity sql. new sql please patient if struggle understand responses , thank in advance taking time @ this.
if understand right, want c0 joined cr , c0 joined cx ( 5 , 4 rows)
yet 5 rows 1 join , 4 rows other join. dbms doesn't know how connect these 2 joins , make little cross join ( every 4 rows 5 other rows) resulting in 20 rows ( if understand description in right way)
here soution 9 rows want.
select ls.sitecode, ep.ep_key, c0.leaseid, c0.sdate leasestart, c0.edate leaseexpiry, cr.effdate renewaldate, 'cr' ls inner join fmep ep on ls.sitecode = ep.sitecode inner join c0 on ep.ep_key = c0.ep_key inner join cr on c0.leaseid = cr.leaseid ls.sitecode = 2121 union select ls.sitecode, ep.ep_key, c0.leaseid, c0.sdate, c0.edate, cx.sdate 'cx' ls inner join fmep ep on ls.sitecode = ep.sitecode inner join c0 on ep.ep_key = c0.ep_key inner join cx on c0.leaseid = cx.leaseid ls.sitecode = 2121 but if want c0 row once, ( eather cx or cr have better join )
Comments
Post a Comment