CODEWITHSUNDEEP
sql
Alfred H.
Alfred H.

Reputation: 103

How to combine 2 queries?

I am Having trouble combining these 2 queries, My question here is how am I suppose to join these 2 queries? Do I use a Join? I want to combine these 2 queries because of certain requirements that I am working on. My use-case here is for showing if there are dates in the sdrp15 return table. Regarding my tables the only thing that links up these 2 queries are the state_code column and phase, these 2 columns are the only columns that show up in all my tables.

Query 1

 select a.phase,a.st_code||' - '||b.state_name AS CHG,
    case when a.submission_received_dt is not null then 'Y' else 'N' end as Changes
    from pcspro.sdrp15_return a,
    pcspro.sdrp15_states_ready b 
    where a.phase = b.phase and a.st_code = b.state;

Result 1:

PHASE  STATE    CHG

   A   01 - AL   Y        
   A   11 - DC   Y     
   A   16 - ID   Y

Query 2 

    select count(cou_code) as changes, state_code 
    from sdrp15_submission_log sl
    where  state_code in (select distinct state_code from sdrp15_submission_log
                          where  state_code = sl.state_code
                          and    cou_code  != 'All')
    and   qa_date  is null
    and   phase = 'A'                    
    group by state_code;

Results 2:

  CHANGES   STATE_CODE   

  -------- ------- 
       29   01          
       2    11        
       2    16

and What I want to do is combining them and my expected results should be:

  PHASE  STATE   CHG   CHANGES

 ------ ------- ------ --------
    A   01 - AL   Y       29 
    A   11 - DC   Y       02
    A   16 - ID   Y       02
    A   08 - HA   Y       NULL

Upvotes: 0

Views: 131

Answers (1)

wralach
wralach

Reputation: 83

Personally, I'd need more info. However, maybe the following will work for you (I haven't tested it!):

SELECT
    a.phase AS PHASE,
    a.st_code||' - '||b.state_name AS [STATE],
    CASE WHEN a.submission_received_dt IS NOT NULL THEN 'Y' ELSE 'N' END AS CHG,
    x.changes AS CHANGES
FROM pcspro.sdrp15_return a
INNER JOIN pcspro.sdrp15_states_ready b
    ON a.phase = b.phase AND a.st_code = b.state
LEFT JOIN (
    SELECT
        COUNT(cou_code) AS changes,
        state_code 
    FROM sdrp15_submission_log sl
    WHERE state_code IN (
        SELECT DISTINCT state_code
        FROM sdrp15_submission_log
        WHERE state_code = sl.state_code AND cou_code  != 'All')
      AND qa_date IS NULL AND phase = 'A'                    
    GROUP BY state_code; 
) x ON x.state_code = a.st_code

Edit according to the comment:

SELECT
    a.phase AS PHASE,
    a.st_code||' - '||b.state_name AS [STATE],
    CASE WHEN a.submission_received_dt IS NOT NULL THEN 'Y' ELSE 'N' END AS CHG,
    x.changes AS CHANGES_qa_date_null,
    y.changes AS CHANGES_qa_date_not_null
FROM pcspro.sdrp15_return a
INNER JOIN pcspro.sdrp15_states_ready b
    ON a.phase = b.phase AND a.st_code = b.state
LEFT JOIN (
    SELECT
        COUNT(cou_code) AS changes,
        state_code 
    FROM sdrp15_submission_log sl
    WHERE state_code IN (
        SELECT DISTINCT state_code
        FROM sdrp15_submission_log
        WHERE state_code = sl.state_code AND cou_code  != 'All')
      AND qa_date IS NULL AND phase = 'A'                    
    GROUP BY state_code; 
) x ON x.state_code = a.st_code
LEFT JOIN (
    SELECT
        COUNT(cou_code) AS changes,
        state_code 
    FROM sdrp15_submission_log sl
    WHERE state_code IN (
        SELECT DISTINCT state_code
        FROM sdrp15_submission_log
        WHERE state_code = sl.state_code AND cou_code  != 'All')
      AND qa_date IS NOT NULL AND phase = 'A'                    
    GROUP BY state_code; 
) y ON y.state_code = a.st_code

Upvotes: 1

Related Questions