CODEWITHSUNDEEP
sqlsql-servert-sql
Mathieu Sigrist
Mathieu Sigrist

Reputation: 3

Joining and grouping queries by priority

I have 2 queries returning data in the same format (year - data - source) I need to join them taking data from q1 and if the year is not present in q1 take it from q2.

Example:

Q1 result set:

    Year - Data - Source
    2014 - 325 - DS1
    2015 - 500 - DS1
    2016 - 450 - DS2

Q2 result set:

    Year - Data - Source
    2016 - 375 - DS4
    2017 - 475 - DS4

Expected result-set:

    Year - Data - Source
    2014 - 325 - DS1 --from q1
    2015 - 500 - DS1 --from q1
    2016 - 450 - DS2 --from q1
    2017 - 475 - DS4 --from q2

Upvotes: 0

Views: 50

Answers (2)

ViKu
ViKu

Reputation: 235

This will also work for you,

Select * from Q1
Union (Select * from Q2 Where Year Not In (Select Year from Q1));

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

A simple way uses union all and some logic:

select q1.year, q1.data, q1.source
from q1
union all
select q2.year, q2.data, q2.source
from q2
where not exists (select 1 from q1 where q1.year = q2.year);

If the subqueries are expensive, you might not want to reference q1 twice. One method uses full join:

select coalesce(q1.year, q2.year) as year,
       coalesce(q1.data, q2,data) as data,
       coalesce(q1.source, q2.source) as source
from q1 full outer join
     q2
     on q1.year = q2.year;

Upvotes: 1

Related Questions