Joining two big queries in sql-CodePudding

I need to join these two queries ....please help

SELECT P.EMAIL_ADDRESS, ECS.FST_NAME, ECS.LAST_NAME, ECS.COMPANY_NAME, UPPER(P.URL) AS URL,
          ECS.COUNTRY, ECS.PER_REGION, ECS.EEA_EMAILABLE_FLG, ECS.PHONEABLE_FLG, ECS.MAILABLE_FLG--INCLUDING THESE FOR INFO ONLY, NOT REQUESTED
              --CAST(P.ACTIVITYDATE AS DATE) PGVIST_DATE
FROM [OPSODSADM].[DBO].[ELQR_PAGEVISIT] P WITH (NOLOCK)
JOIN [OPSSOAADM].[DBO].[ELOQUA_CONTACT_STAGE] ECS WITH (NOLOCK) ON P.EMAIL_ADDRESS = ECS.EMAIL_ADDRESS  --JOINING TO GET NAME AND COMPANY FIELDS
WHERE ACTIVITYDATE >= '2022-01-01' --STARTING DATE INCLUDED IN MARS REQUEST
         AND CHARINDEX('GARTNER.COM', UPPER(P.EMAIL_ADDRESS),1) = 0  --DON'T WANT GARTNER CONTACTS
         AND CHARINDEX('RAINFOCUS', UPPER(P.EMAIL_ADDRESS),1) = 0  --DON'T WANT RAINFOCUS CONTACTS
         AND (ECS.EVT_EXCL_FLG IS NULL OR ECS.EVT_EXCL_FLG = 'N') --DON'T WANT TO INCLUDE CONTACTS WITH ACTIVE/CURRENT REGISTRATIONS
         AND ECS.PER_REGION = 'NA'
         AND (UPPER(URL) = 'HTTPS://WWW.GARTNER.COM/EN/CONFERENCES/NA/CFO-FINANCE-US/REGISTER')
         --AND CHARINDEX('FES22/RFR', UPPER(URL),1) > 0 --RUN TO SEE ALL VARIATIONS OF RF PAGES BUT ONLY THE RFR/ORDER URL IS WHAT WE TARGET FOR CART ABANDONS
GROUP BY P.EMAIL_ADDRESS, ECS.FST_NAME, ECS.LAST_NAME, ECS.COMPANY_NAME, UPPER(P.URL),
              ECS.COUNTRY, ECS.PER_REGION, ECS.EEA_EMAILABLE_FLG, ECS.PHONEABLE_FLG, ECS.MAILABLE_FLG
----ORDER BY P.EMAIL_ADDRESS




SELECT P.EMAIL_ADDRESS, ECS.FST_NAME, ECS.LAST_NAME, ECS.COMPANY_NAME, UPPER(P.URL) AS URL,
          ECS.COUNTRY, ECS.PER_REGION, ECS.EEA_EMAILABLE_FLG, ECS.PHONEABLE_FLG, ECS.MAILABLE_FLG --INCLUDING THESE FOR INFO ONLY, NOT REQUESTED
              --CAST(P.ACTIVITYDATE AS DATE) PGVIST_DATE
FROM [OPSODSADM].[DBO].[ELQR_PAGEVISIT] P WITH (NOLOCK)
JOIN [OPSSOAADM].[DBO].[ELOQUA_CONTACT_STAGE] ECS WITH (NOLOCK) ON P.EMAIL_ADDRESS = ECS.EMAIL_ADDRESS  --JOINING TO GET NAME AND COMPANY FIELDS
WHERE ACTIVITYDATE >= '2022-01-01' --STARTING DATE INCLUDED IN MARS REQUEST
         AND CHARINDEX('GARTNER.COM', UPPER(P.EMAIL_ADDRESS),1) = 0  --DON'T WANT GARTNER CONTACTS
         AND CHARINDEX('RAINFOCUS', UPPER(P.EMAIL_ADDRESS),1) = 0  --DON'T WANT RAINFOCUS CONTACTS
         AND (ECS.EVT_EXCL_FLG IS NULL OR ECS.EVT_EXCL_FLG = 'N') --DON'T WANT TO INCLUDE CONTACTS WITH ACTIVE/CURRENT REGISTRATIONS
         AND ECS.PER_REGION = 'NA'
         AND (UPPER(URL) = 'HTTPS://REG.GARTNER.COM/FLOW/GARTNER/FES22/RFR/ORDER')
         --AND CHARINDEX('FES22/RFR', UPPER(URL),1) > 0 --RUN TO SEE ALL VARIATIONS OF RF PAGES BUT ONLY THE RFR/ORDER URL IS WHAT WE TARGET FOR CART ABANDONS
GROUP BY P.EMAIL_ADDRESS, ECS.FST_NAME, ECS.LAST_NAME, ECS.COMPANY_NAME, UPPER(P.URL),
              ECS.COUNTRY, ECS.PER_REGION, ECS.EEA_EMAILABLE_FLG, ECS.PHONEABLE_FLG, ECS.MAILABLE_FLG
--ORDER BY P.EMAIL_ADDRESS
ORDER BY P.EMAIL_ADDRESS

CodePudding user response：

If you want a quick and dirty solution, you could make both queries common table expressions and then join them as you would two normal tables:

with Tab1 AS ([YOUR FIRST BIG QUERY])
,Tab2 AS ([YOUR SECOUND BIG QUERY])

SELECT * FROM Tab1 INNER JOIN Tab2 ON Tab1.[FIELD] = Tab2.[FIELD]

If you want something that will not look so unpleasant, I would recommend taking the URL out and making it a variable, so you can call the script more succinctly and go from there, but that is very reliant on context.