CODEWITHSUNDEEP
sqlsql-server
BSanders
BSanders

Reputation: 295

Can I combine these two sql queries into one query?

The first query is all the info that I need for companies within a 15 mile radius. 

 SELECT DISTINCT CI.co,
                CI.name,
                CI.address1,
                CI.address2,
                CI.city,
                CI.state,
                CI.zip,
                CI.contact1,
                CI.contact1email,
                CI.contact2,
                CI.contact2email,
                CI.contact3,
                contact3email,
                Count(EI.id) AS ActiveEE
FROM   cinfo CI
       INNER JOIN einfo EI
               ON CI.co = EI.co
WHERE  NOT CI.co IN (SELECT co
                     FROM   scompanysetdetail
                     WHERE  companyset = 'REF-GCohen')
       AND enddate IS NULL
       AND EI.empstatus = 'A'
       AND CI.zip IN ( *zip codes for the 15 mile radius* )
GROUP  BY CI.co,
          CI.name,
          CI.address1,
          CI.address2,
          CI.city,
          CI.state,
          CI.zip,
          CI.contact1,
          CI.contact1email,
          CI.contact2,
          CI.contact2email,
          CI.contact3,
          CI.contact3email

The 2nd query gives me the top 10 paid employees by company

 WITH cterownum
     AS (SELECT co,
                id,
                ename,
                title,
                hiredate,
                salary,
                Dense_rank()
                  OVER(
                    partition BY co
                    ORDER BY salary DESC) AS RowNum
         FROM   cps_wss_emplist)
SELECT *
FROM   cterownum
WHERE  rownum <= 10
ORDER  BY co,
          rownum ASC

How can I combine these two queries into one?

Upvotes: 0

Views: 188

Answers (3)

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

;WITH FirstCTE AS
(
SELECT DISTINCT CI.co,
            CI.name,
            CI.address1,
            CI.address2,
            CI.city,
            CI.state,
            CI.zip,
            CI.contact1,
            CI.contact1email,
            CI.contact2,
            CI.contact2email,
            CI.contact3,
            contact3email,
            Count(EI.id) AS ActiveEE
FROM   cinfo CI
   INNER JOIN einfo EI
           ON CI.co = EI.co
WHERE  NOT CI.co IN (SELECT co
                 FROM   scompanysetdetail
                 WHERE  companyset = 'REF-GCohen')
   AND enddate IS NULL
   AND EI.empstatus = 'A'
   AND CI.zip IN ( *zip codes for the 15 mile radius* )
GROUP  BY CI.co,
      CI.name,
      CI.address1,
      CI.address2,
      CI.city,
      CI.state,
      CI.zip,
      CI.contact1,
      CI.contact1email,
      CI.contact2,
      CI.contact2email,
      CI.contact3,
      CI.contact3email  
)
,
SecondCTE AS
(
SELECT co,
            id,
            ename,
            title,
            hiredate,
            salary,
            Dense_rank()
              OVER(
                partition BY co
                ORDER BY salary DESC) AS RowNum
     FROM   cps_wss_emplist
),
ThirdCTE AS
(
SELECT *
FROM   cterownum
WHERE  rownum <= 10
)
SELECT    *
FROM        FirstCTE F
               JOIN ThirdCTE C
                   ON F.Co = S.Co

Upvotes: 3

Devart
Devart

Reputation: 121922

Try this one -

;WITH cterownum AS
(
    SELECT co
         , id
         , ename
         , title
         , hiredate
         , Salary
         , RowNum = DENSE_RANK() OVER (PARTITION BY co ORDER BY Salary DESC) 
    FROM cps_wss_emplist
)
SELECT  
      CI.*
    , EI.ActiveEE
    , ttt.*
FROM CInfo CI
JOIN (
    SELECT co, ActiveEE = COUNT(id)
    FROM EInfo
    WHERE empStatus = 'A'
    GROUP BY co
) EI ON CI.co = EI.co
JOIN cterownum ttt ON CI.co = ttt.co
WHERE NOT CI.co IN (
        SELECT co
        FROM SCompanySetDetail
        WHERE companySet = 'REF-GCohen'
    )
    AND EndDate IS NULL
    AND CI.zip IN ('')
    AND rownum <= 10
ORDER BY co, rownum

Upvotes: 2

ganders
ganders

Reputation: 7432

For the easy way out, I'd just make both of those subqueries and join the results together, then list out all of the fields:

    Select -- list all your fields that exist in both temp tables

From
(
    SELECT DISTINCT CI.co,
        CI.name,
        CI.address1,
        CI.address2,
        CI.city,
        CI.state,
        CI.zip,
        CI.contact1,
        CI.contact1email,
        CI.contact2,
        CI.contact2email,
        CI.contact3,
        contact3email,
        Count(EI.id) AS ActiveEE
    FROM   cinfo CI
    INNER JOIN einfo EI ON CI.co = EI.co
    WHERE  NOT CI.co IN
    (
        SELECT co
        FROM   scompanysetdetail
        WHERE  companyset = 'REF-GCohen')
        AND enddate IS NULL
        AND EI.empstatus = 'A'
        AND CI.zip IN ( *zip codes for the 15 mile radius* )
    )
    GROUP  BY CI.co,
              CI.name,
              CI.address1,
              CI.address2,
              CI.city,
              CI.state,
              CI.zip,
              CI.contact1,
              CI.contact1email,
              CI.contact2,
              CI.contact2email,
              CI.contact3,
              CI.contact3email 
) a
Inner Join
(         
    ;WITH cterownum
         AS (SELECT co,
                    id,
                    ename,
                    title,
                    hiredate,
                    salary,
                    Dense_rank()
                      OVER(
                        partition BY co
                        ORDER BY salary DESC) AS RowNum
             FROM   cps_wss_emplist)
    SELECT *
    FROM   cterownum
    WHERE  rownum <= 10
    ORDER  BY co,
              rownum ASC
) b On b.co = a.co

Upvotes: 1

Related Questions