Loading ...
Sorry, an error occurred while loading the content.

4901RE: [SQLQueriesNoCode] Combining 2 queries - correct Join or SubQuery sntax

Expand Messages
  • seniorprogrammerguru
    Mar 12, 2014
      Its a home brewed cms I am  migrating to Umbraco which does versioning well.

      Sent from my T-Mobile 4G LTE Device

      -------- Original message --------
      From: John Warner <john@...>
      Date: 03/12/2014 4:27 PM (GMT-05:00)
      To: SQLQueriesNoCode@yahoogroups.com
      Subject: RE: [SQLQueriesNoCode] Combining 2 queries - correct Join or SubQuery sntax


      I know this doesn’t solve your current problem but given what you mention regarding versioning, I wonder if in the future this client would be happier with something like Source Safe/Team Foundation or svn or Git. As you well know roll backs to older versions in these tools is easy and fast.


      John Warner


      From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Charles Carroll
      Sent: Wednesday, March 12, 2014 3:56 PM
      To: sqlqueriesnocode@yahoogroups.com
      Subject: [SQLQueriesNoCode] Combining 2 queries - correct Join or SubQuery sntax


      Looking to get this syntax right me and a few co-workers took  a few
      stabs and failed. Afraid to show the failed ones, since it might confuse the issue.

          FROM [sitepublisherii].[dbo].[


      is a query that does exactly what I need a simple select that produces
      23k records x 18 fields.

      Then I have a more complex query that is fed by
      [DocId] => UmbracoDocs_ID
      above (see bottom)

      But the output it is a straight 1:1 + the fields below. If the query syntax is done correctly...
      23k of  records with 24 fields (18 fields + these 6)
      a 1:1 lookup

      will be the result

      DETAILS: The more complex query if execuhed alone more than 23k
      records, since basically DocPageID is a INT that reflects each
      document version. And there are old doc versions that are no longer
      relevant kept in case they want to revert to earlier versions. If a
      person edited a Dcoument 12 times, it would have 12 rows in the
      Docpages database. But I only need the DocPageID that is the latest
      greatest one which is what the 23k DB query above produces.

              CAST(CAST(N'' AS
              ,a.HtmlPage) ASCIIEncoding
              DP.DocPageID As UmbracoDocs_ID

               ,Convert(Varchar(max),DP.DocPage) As HtmlPage

              ,D.Createdby_PersID As Person_ID

              --INTO #UmbracoDocs

              from DocPages DP

              inner join Documents D on DP.DocPageId=D.DocId

              inner join NavSystems NS on D.OriginSite_ID=NS.OriginSite_ID

              inner join Navigation N on NS.NavSysID=N.NavSys_ID

          ) A;

    • Show all 5 messages in this topic