CODEWITHSUNDEEP
sqldatabasejoinview
hampusohlsson
hampusohlsson

Reputation: 10219

SQL to join two views

Need some help joining these two tables

I have two views that looks like this

view1                view2
+------+--------+    +------+--------+
| code | SUM(*) |    | code | SUM(*) |
+------+--------+    +------+--------+
| AAA  |      4 |    | AAA  |      4 |
| BBB  |      3 |    | CCC  |      1 |
+------+--------+    +------+--------+

I want to join them into a table that looks like this

+------+--------+
| code | SUM(*) |
+------+--------+
| AAA  |      4 |
| BBB  |      3 |
| CCC  |      1 |    
+------+--------+

I have tried, but only failed..

Upvotes: 2

Views: 33555

Answers (3)

Adam Robinson
Adam Robinson

Reputation: 185703

For your first result, the answers posted using union will do the trick:

select * from view1
union
select * from view2

However, given the fact that one of your columns is a sum, this seems unlikely to be what you actually want.

For your second result (where the values are added), you'll need to use a union and a subquery:

select
    code,
    sum(yourcol)

from
(
    select
        code,
        yourcol

    from view1

    union all

    select
        code,
        yourcol

    from view2
) source

group by code

Upvotes: 5

user596075
user596075

Reputation: 

select *
from view1
union
select *
from view2

Utilizing a UNION will not return duplicate entries, which is what it seems you are looking for.

Upvotes: 8

halfdan
halfdan

Reputation: 34274

You can use a UNION for this:

SELECT * FROM view1
UNION DISTINCT
SELECT * FROM view2

Upvotes: 6

Related Questions