Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: My own little WTF join

    Ok. Any ideas on what would posess somebody to write a join like this?:
    Code:
    	  FROM dbo.AR_Invoice_Header Header
    	  LEFT OUTER JOIN dbo.AR_Invoice_Line Line
    		  LEFT OUTER JOIN dbo.AR_Items Item 
    			LEFT OUTER JOIN dbo.AR_Units Unit
    			  ON Item.ITM_UNT_ID = Unit.UNT_ID
    		    ON Item.ITM_ID = Line.ILN_ITM_ID
    		  LEFT OUTER JOIN dbo.AR_Line_Of_Business LOB
    		    ON LOB.LOB_ID = ILN_LOB_ID
    	     ON Header.IHD_ID = Line.ILN_IHD_ID
    	  JOIN dbo.AR_Clients ACL
    	    ON ACL.ACL_ID = Header.IHD_ACL_ID
    I see this stuff occasionally, and just think "WTF?"
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Are we talking formatting over logical join here?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I had 2 developers who wrote like this. one worked every hour he was awake and he hardly slept. he just disappeared one day and his voice mail on his cell was full. no one heard from him for about a month and then I heard whispers from his friends about drugs and a troubled marriage.

    I just concluded that your code is a reflection of who you are.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I wasn't aware such syntax was even allowed. separating the ON from the JOIN that way is very strange. props for unmaintainability though!

    LEFT OUTER JOIN dbo.AR_Items Item
    LEFT OUTER JOIN dbo.AR_Units Unit
    ON Item.ITM_UNT_ID = Unit.UNT_ID
    ON Item.ITM_ID = Line.ILN_ITM_ID

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i see those occasionally

    not quite as WTF as the ones with LEFTs and RIGHTs

    my conclusion: some people should not be let near a computer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    my conclusion: some people should not be let near a computer
    That's what they tell me at work after they hide my laptop but I've always managed to find it again. This morning I had to remove some of the roofing tiles.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you are running VISTA, do NOT remove the roofing tiles. They are a vital part of the virus protection built into the operating system.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It seems a bit similar to something I had to do to get the differences between two versions of a database. I used parentheses to separate the moving parts, though...

    Code:
    from (dbinstances idv join
    	databases ddv on idv.dbinstanceid = ddv.dbinstanceid and idv.name = 'sqldev3' join
    	dbtables tdv on ddv.databaseid = tdv.databaseid and ddv.name = 'CFIT' join
    	dbcolumns cdv on cdv.dbtableid = tdv.dbtableid) 
    
    full outer join
    
    	(dbinstances ipd join
    	databases dpd on ipd.dbinstanceid = dpd.dbinstanceid and ipd.name = 'sqlprd3' join
    	dbtables tpd on dpd.databaseid = tpd.databaseid and dpd.name = 'CFIT' join
    	dbcolumns cpd on cpd.dbtableid = tpd.dbtableid) on cdv.name = cpd.name and tdv.name = tpd.name
    All it does is compare the column and table names between databases on two separate servers. One of the benefits of dumping all column and table information into a central database.

  9. #9
    Join Date
    Sep 2005
    Posts
    161
    To me, this wreaks of code generated by a graphical query designer. Perhaps the coder started with generated SQL and then modified it.

  10. #10
    Join Date
    Nov 2005
    Posts
    122
    Agree with cascred. I have seen lots of WTF sql generated by the query designer in Enterprise Manager. Yes, I used the query design in the beginning on SQL Server 7 and a bit on 2000 when I was starting to learn SQL Server.

    Don't know if SSMS generates the same "crap".

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pootle flump
    That's what they tell me at work after they hide my laptop but I've always managed to find it again. This morning I had to remove some of the roofing tiles.

    at least be gratefull they didn't decide to use the laptop in place of the roofing tiles

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    They should have done that at our place last year before one of the offices had a major roof leak: The ceiling tiles broke and a torrent of water destroyed 6 PCs...
    George
    Home | Blog

  13. #13
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I had lightenening strike a power pole out side of a small office I was working in once. power strips were shooting flames and sparks and it blew out the power supplies in half of the PCs in the office. It looked like an old school Star Trek battle scene with the smoke and sparks.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  14. #14
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    was everyone thrown suddenly to the right of the set, and then to the left?

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I bet the only casualties were short term contractors and security guards too
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •