Join two identically structured views

mah01 · #1 (**permalink**) 09-02-08, 08:03

Hi

I have two views that contain the same columns of the same types. They also have the same amount of rows with the same primary keys.

E.g.

Code:

VIEW_A
ID	VALUE
1	(null)
2	1001
3	(null)

VIEW_B
ID	VALUE
1	81007941
2	(null)
3	(null)

I would like to create a third view VIEW_COMBO that combines the two views, getting as many values as possible. I.e. getting the value 81007941 from VIEW_B and the value 1001 from VIEW_A and (null) for the third row where no value exists.

E.g.

Code:

VIEW_COMBO
ID	VALUE
1	81007941
2	1001
3	(null)

Is this possible somehow to write a sql query that does that?

Best regards,
Mattias

ARWinner · #2 (**permalink**) 09-02-08, 08:11

Look at UNION and UNION ALL in the documentation.

Andy

mah01 · #3 (**permalink**) 09-02-08, 08:38

Thanks for your suggestion. I've checked the documentation on UNION ALL and tried the following:

Code:

CREATE VIEW VIEW_COMBO AS 
SELECT * FROM VIEW_A 
UNION ALL 
SELECT * FROM VIEW_B
WITH ROW MOVEMENT;

The view is created and contains the following:

Code:

SELECT * FROM VIEW_COMBO;
ID	VAL
1	(null)
2	2000
3	(null)
1	1000
2	(null)
3	(null)

So now I get allt the rows in the new view, but this is not really what I wanted. I wanted it to end up like this:

Code:

ID	VAL
1	1000
2	2000
3	(null)

Is that possible?

Best regards, Mattias

stolze · #4 (**permalink**) 09-02-08, 08:44

You need to use UNION instead of UNION ALL if you want to eliminate duplicate rows.

But I believe that you have semantics like this: For each ID value, use the VAL value that is not NULL. Right? What happens if the same ID value occurs in both tables and the VAL value is not NULL in both? Which one do you pick? If we can just take an arbitrary one (or the one from the first table), this would do - untested:

Code:

SELECT COALESCE(t1.id, t2.id), COALESCE(t1.val, t2.val)
FROM   t1 LEFT OUTER JOIN t2 ON t1.id = t2.id

mah01 · #5 (**permalink**) 09-02-08, 08:50

Quote:

Originally Posted by stolze

You need to use UNION instead of UNION ALL if you want to eliminate duplicate rows.

But I believe that you have semantics like this: For each ID value, use the VAL value that is not NULL. Right? What happens if the same ID value occurs in both tables and the VAL value is not NULL in both? Which one do you pick? If we can just take an arbitrary one (or the one from the first table), this would do - untested:

Code:

SELECT COALESCE(t1.id, t2.id), COALESCE(t1.val, t2.val)
FROM   t1 LEFT OUTER JOIN t2 ON t1.id = t2.id

Yes! That's exactly what I want to do, and your suggestion works like a charm

Problem solved! Thank you very much!

Best regards,
Mattias

stolze · #6 (**permalink**) 09-02-08, 09:53

Btw, you may want to use a FULL OUTER JOIN instead of only a LEFT OUTER JOIN. Otherwise, you wouldn't get any ID values from the table on the right side of the join if they are only in that table.

mah01 · #7 (**permalink**) 09-02-08, 09:55

Quote:

Originally Posted by stolze

Btw, you may want to use a FULL OUTER JOIN instead of only a LEFT OUTER JOIN. Otherwise, you wouldn't get any ID values from the table on the right side of the join if they are only in that table.

Thanks, the ID values will always exist in all rows in both left and right table though.