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

4902Re: Combining 2 queries - correct Join or SubQuery sntax

Expand Messages
  • Charles Carroll
    Mar 12, 2014
    • 0 Attachment
      While shopping at Costco I sloved it mentally and can simplify the whole thing in the morning so never mind.

      This whole thing started because my colleague wrote the Base64 query with a few joins I tried to fix and did. But once I looked at the whole thing in my brain I realized almost all the joins are irrelevant there is a much simpler way I will share with the list in the morning.

      Back at home and preparing to teach a Xamarin class tomorrow. That will consume most of my night.

      On Wed, Mar 12, 2014 at 3:56 PM, Charles Carroll <seniorprogrammerguru@...> wrote:
      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