SQL to join two views

Question

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..

What have you tried? Maybe someone can determine why it failed. Looks to me like a UNION DISTINCT. — Wiseguy
– Wiseguy, Commented Nov 20, 2011 at 1:32
Can you give more detail on what SQL you are trying and what the error or results are? Also what database are you using? Generally you can join VIEWS in the same manner you JOIN normal tables (so simple LEFT JOIN or UNION clauses). — catchdave
– catchdave, Commented Nov 20, 2011 at 1:33
Should SUM(*) in your result be 4 (as it is now) or 8 (adding the two values for AAA together)? — Adam Robinson
– Adam Robinson, Commented Nov 20, 2011 at 1:35
UNION was what I was looking for, I kept trying with different JOIN/DISTINCT clauses. @AdamRobinson it should not add the values. — hampusohlsson
– hampusohlsson, Commented Nov 20, 2011 at 1:37

user596075 · Accepted Answer · 2011-11-20 01:34:00Z

8

select *
from view1
union
select *
from view2

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

answered Nov 20, 2011 at 1:34

user596075

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

Comments

halfdan · Accepted Answer · 2011-11-20 01:33:43Z

6

You can use a UNION for this:

SELECT * FROM view1
UNION DISTINCT
SELECT * FROM view2

answered Nov 20, 2011 at 1:33

halfdan

34.3k8 gold badges83 silver badges88 bronze badges

Comments

Adam Robinson · Accepted Answer · 2011-11-20 01:54:43Z

5

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

edited Nov 20, 2011 at 1:54

answered Nov 20, 2011 at 1:35

Adam Robinson

186k36 gold badges294 silver badges351 bronze badges

Collectives™ on Stack Overflow

SQL to join two views

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related