I've "inherited" a well over 10-year old app, and it does show its age at times. I've stumbled across a really weird view definition today - I just can't seem to make sense of it! Can you help me? This was originally on SQL Server 7.0 and has since been migrated to SQL Server 2005 - but obviously it's never been refactored / redone....
This is the view definition - based on a bunch of tables and another view:
CREATE VIEW dbo.MyOddView
AS
SELECT
t1.MVOID, t1.SomeOtherColumn,
t2.Number ,
t3.OID, t3.FKOID,
t4.AcctNo,
t5.ShortDesc, t5.ZipCode, t5.City,
t6.BankAcctNo
FROM
dbo.viewFirst vf
INNER JOIN
dbo.Table1 t1 ON vf.MVOID = t1.MVOID AND vf.ValidFrom = t1.ValidFrom
LEFT OUTER JOIN
dbo.Table2 t2
RIGHT OUTER JOIN
dbo.Table3 t3 ON t2.OID = t3.FKOID
LEFT OUTER JOIN
dbo.Table4 t4 ON t3.ZVOID = t4.OID
LEFT OUTER JOIN
dbo.Table5 t5
INNER JOIN
dbo.Table4 t6 ON t5.OID = t6.BCOID
ON t4.ZVOID = t5.OID
ON t2.AddressOID = t4.OID
GO
What I don't get are the two JOIN's (for Table2 t2
and Table5 t5
) which have no JOIN condition listed next to them, and the two extra ON
conditions at the end of the view definition - I can't seem to rip this apart and put it back together in "proper" ANSI JOIN syntax so that my row count is the same...... (my original view gets me something over 12'000 rows, and a first attempt at refactoring this returned a single row......)
Any ideas? What the heck is this? Seems like totally invalid SQL to me - but it appears to be doing its job (and has been for the past several years....) Any thoughts? Pointers?