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.

        Select
               [DocId]
              ,[Title]
              ,[Createdby_PersID]
              ,[CurrentVersion_ID]
              ,[DocType]
              ,[Doc]
              ,[Published]
              ,[CG_ID]
              ,[DocOrder]
              ,[OriginSite_Type]
              ,[OriginSite_ID]
              ,[keywords]
              ,[oldURL]
              ,[RLNav]
              ,[oldUrl_redirect_timer]
              ,[HTMLHeader]
              ,[DisplayTitle]
          FROM [sitepublisherii].[dbo].[
      Documents]

      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
              HtmlPage
              ASCIIEncoding
              Person_ID
              NavURL
              NavLabel
              OriginSite_ID
      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.

          select
               A.UmbracoDocs_ID
              ,A.HtmlPage
              ,ISNULL(
              CAST(CAST(N'' AS
      XML).value('xs:base64Binary(sql:column("A.HtmlPage"))',
      'VARBINARY(MAX)') AS VARCHAR(MAX))
              ,a.HtmlPage) ASCIIEncoding
              ,A.Person_ID
              ,A.NavURL
              ,A.NavLabel
              ,A.OriginSite_ID
           from
          (
          select
              DP.DocPageID As UmbracoDocs_ID

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

              ,D.Createdby_PersID As Person_ID
              ,N.NavURL
              ,N.NavLabel
              ,D.OriginSite_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