How do I combine multiple queries?

Question

I need to combine two result sets and I feel I am so close but just don't see how to wrap this all up:

Here's one query with a small result set, (give me inactives)

SELECT MAX(set_date) AS most_recent_inactive, key_value, statusid
FROM status_history
WHERE base_table = 'userinfo'  
AND statusid = 10 AND set_date > TO_DATE('2012-10-01', 'YYYY-MM-DD')
GROUP BY key_value, statusid

Output:

recent_inactive key_value statusid
2013-01-30  15  10
2013-06-04  261 10
2013-06-18  352 10
2012-10-04  383 10
2013-01-22  488 10
2013-03-04  711 10
2013-06-19  749 10
2013-03-05  806 10

Another query with a small result set (give me actives)

SELECT MAX (set_date) AS most_recent_active, key_value, statusid
FROM status_history
WHERE base_table = 'userinfo'  
AND statusid =11
GROUP BY key_value, statusid

Output:

recent_active key_value statusid
2002-01-01  3   11
2002-01-01  5   11
2002-01-01  14  11
2002-01-01  15  11
2002-01-01  21  11
2002-01-01  23  11
2002-01-01  25  11
2002-01-01  26  11

I want to get all of the actives and inactives together, so I union them all

SELECT NULL AS most_recent_active, MAX(set_date) AS most_recent_inactive, key_value, statusid
FROM status_history
WHERE base_table = 'userinfo'  
AND statusid = 10 AND set_date > TO_DATE('2012-10-01', 'YYYY-MM-DD')
GROUP BY key_value, statusid
    UNION all
SELECT MAX(set_date) AS most_recent_active, NULL AS most_recent_inactive, key_value, statusid
FROM status_history
WHERE base_table = 'userinfo'  
AND statusid = 11
GROUP BY key_value, statusid
ORDER by key_value

Output:

recent_active recent_inactive key_value statusid
2002-01-01  null    3   11
2002-01-01  null    5   11
2002-01-01  null    14  11
null  2013-01-30    15  10
2002-01-01  null    15  11
2002-01-01  null    21  11
2002-01-01  null    23  11
2002-01-01  null    25  11
2002-01-01  null    26  11
2002-01-01  null    27  11
2002-01-01  null    29  1

The problem is key_value 15 is duplicated.

The values are correct, but i want that record and all subsequent duplicates "flattened," row 15 and all other matches coming through as one record with both date fields set.

Again, I feel I'm so close but how do I wrap this all up?

Thank you?

Which status id are you going to want when you flatten row 15? — CharlesC
– CharlesC, Commented Sep 16, 2013 at 18:24
I need statusid of 10. But, if recent_inactive has a value, statusid will always be 10 because that's by definition, I have an inactive date because they're inactive, statusid 10. — dazed-and-confused
– dazed-and-confused, Commented Sep 16, 2013 at 18:26

Ben · Accepted Answer · 2013-09-16 18:43:06Z

3

You can use a CASE statement to split out which are inactive and which are active. The default, if the case is not fulfilled is NULL so you'll get what you want.

select max(case when statusid = 10 then set_date end) as most_recent_inactive
     , max(case when statusid = 11 then set_date end) as most_recent_active
     , key_value
     , max(statusid) as statusid
  from status_history
 where base_table = 'userinfo'  
   and statusid in (10, 11)
 group by key_value, statusid

The date thing you've got going on in the inactive is a little strange but if you want to restrict just put this in the CASE for inactive dates:

select max(case when statusid = 10  
                      and set_date > to_date('2012-10-01', 'YYYY-MM-DD')
                     then set_date 
           end) as most_recent_inactive
     , max(case when statusid = 11 then set_date end) as most_recent_active
     , key_value
     , max(statusid) as statusid
  from status_history
 where base_table = 'userinfo'  
   and statusid in (10, 11)
 group by key_value, statusid

I've assumed that if something is both active and inactive you want to display that it's active. If you want to display it as inactive use min(statusid); if you want to display both then you need another column... follow the same logic; use a CASE statement. If you want neither then remove it from the SELECT and GROUP BY clauses completely.

edited Sep 16, 2013 at 18:43

answered Sep 16, 2013 at 18:24

Ben

53.1k36 gold badges133 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

dazed-and-confused Over a year ago

The first query isn't good because I'm getting multiple results for key_value. Second query, missing expression.

Ben Over a year ago

If something's inactive and active do you want it to be inactive or active @dazed-and-confused? The second is really simple; I have two ands, you could just remove one... but it would come up with the same result.

dazed-and-confused Over a year ago

The statusid doesn't matter. The inactive_date tells me I'm inactive. I just need the dates.

Ben Over a year ago

I've altered my answer; if the statusid doesn't matter then just remove it. That's what's causing your problems.

dazed-and-confused Over a year ago

This gives me the correct result I want. The other answer does as well but this is cleaner as it doesn't have my ugly union. Now I hope I can properly join this result set with the larger outer set (not mentioned here). Thanks

|

CharlesC · Accepted Answer · 2013-09-16 18:42:11Z

1

This is assuming that your inactive status_id is always less than your active status_id. This may not work if there are other possible status_id values.

select max(most_recent_active), max(most_recent_inactive), key_value, min(status_id)
from (select null as most_recent_active, max(set_date) as most_recent_inactive, key_value,statusid
from status_history
where base_table = 'userinfo'  
and statusid = 10 and set_date > to_date('2012-10-01', 'YYYY-MM-DD')
group by key_value,statusid
    UNION all
select max(set_date) as most_recent_active, null as most_recent_inactive, key_value,statusid
from status_history
where base_table = 'userinfo'  
and statusid = 11
group by key_value,statusid
order by key_value)
group by key_value

edited Sep 16, 2013 at 18:42

answered Sep 16, 2013 at 18:29

CharlesC

3503 silver badges8 bronze badges

Comments

PM 77-1 · Accepted Answer · 2013-09-16 18:39:23Z

0

Do GROUP BY after the union.

SELECT MAX(active) As most_recent_active,  MAX(inactive) As most_recent_in_active,
       key_value, statusid
FROM
(SELECT null as active, set_date as inactive, key_value, statusid
 FROM status_history
 WHERE base_table = 'userinfo'  
       AND statusid = 10 and set_date > to_date('2012-10-01', 'YYYY-MM-DD')

    UNION ALL

 SELECT set_date as active, null as inactive, key_value, statusid
 FROM status_history
 WHERE base_table = 'userinfo'  
       AND statusid = 11)

 GROUP BY key_value, statusid
 ORDER BY by key_value

edited Sep 16, 2013 at 18:39

answered Sep 16, 2013 at 18:24

PM 77-1

13.4k21 gold badges73 silver badges118 bronze badges

Collectives™ on Stack Overflow

How do I combine multiple queries?

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related