Merge two unrelated views into a single view

Question

Let's say I have in my first view (ClothingID, Shoes, Shirts) and in the second view I have (ClothingID, Shoes, Shirts) HOWEVER the data is completely unrelated, even the ID field is not related in anyway. I want them combined into 1 single view for reporting purposes. so the 3rd view (the one I'm trying to make) should look like this: (ClothingID, ClothingID2, Shoes, Shoes2, Shirts, Shirts2) so there's no relation AT ALL, I'm just putting them side by side, unrelated data into the same view.

Any help would be strongly appreciated

Solution 1

You want to combine the results, yet be able to tell the rows apart.
To duplicate all columns would be a bit of an overkill. Add a column with info about the source:

SELECT 'v1'::text AS source, clothingid, shoes, shirts
FROM   view1

UNION  ALL
SELECT 'v2'::text AS source, clothingid, shoes, shirts
FROM   view2;

Solution 2

select v1.ClothingID, v2.ClothingID as ClothingID2, v1.Shoes, v2.Shoes as Shoes2,
    v1.Shirts, v2.Shirts as Shirts2
from (
    select *, row_number() OVER (ORDER BY ClothingID) AS row
    from view_1
) v1
full outer join (
    select *, row_number() OVER (ORDER BY ClothingID) AS row
    from view_2
) v2 on v1.row = v2.row

I think that full outer join that joins table using new unrelated column row will do the job.

row_number() exists in PostgreSQL 8.4 and above.

If you have lower version you can imitate row_number, example below. It's going to work only if ClothingID is unique in a scope of view.

select v1.ClothingID, v2.ClothingID as ClothingID2, v1.Shoes, v2.Shoes as Shoes2,
    v1.Shirts, v2.Shirts as Shirts2
from (
    select *, (select count(*) from view_1 t1 
        where t1.ClothingID <= t.ClothingID) as row
    from view_1 t
) v1
full outer join (
    select *, (select count(*) from view_2 t2 
        where t2.ClothingID <= t.ClothingID) as row
    from view_2 t
) v2 on v1.row = v2.row

Added after comment:

I've noticed and corrected mistake in preceding query.

I'll try to explain a bit. First of all we'll have to add a row numbers to both views to make sure that there are no gaps in id's. This is quite simple way:

select *, (select count(*) from view_1 t1 
    where t1.ClothingID <= t.ClothingID) as row
from view_1 t

This consist of two things, simple query selecting rows(*):

select *
from view_1 t

and correlated subquery (read more on wikipedia):

(
    select count(*)
    from view_1 t1
    where t1.ClothingID <= t.ClothingID
) as row

This counts for each row of outer query (here it's (*)) preceding rows including self. So you might say count all rows which have ClothingID less or equal like current row for each row in view. For unique ClothingID (that I've assumed) it gives you row numbering (ordered by ClothingID).

Live example on data.stackexchange.com - row numbering.

After that we can use both subqueries with row numbers to join them (full outer join on Wikipedia), live example on data.stackexchange.com - merge two unrelated views.

Solution 3

If the views are unrelated, SQL will struggle to deal with it. You can do it, but there's a better and simpler way...

I suggest merging them one after the other, rather than side-by-side as you have suggested, ie a union rather than a join:

select 'view1' as source, ClothingID, Shoes, Shirts
from view1
union all
select 'view2', ClothingID, Shoes, Shirts
from view2

This would be the usual approach for this kind of situation, and is simple to code and understand.

Note the use of UNION ALL, which preserves row order as selected and does not remove duplicates, as opposed to UNION, which sorts the rows and removes duplicates.

Edited

Added a column indicating which view the row came from.

Solution 4

You could use Rownumber as a join parameter, and 2 temp tables?

So something like:

    Insert @table1
    SELECT ROW_NUMBER() OVER (ORDER BY t1.Clothing_ID ASC) [Row_ID], Clothing_ID, Shoes, Shirts)
    FROM Table1 

    Insert @table2
    SELECT ROW_NUMBER() OVER (ORDER BY t1.Clothing_ID ASC)[RowID], Clothing_ID, Shoes, Shirts)
    FROM Table2

    Select  t1.Clothing_ID, t2.Clothing_ID,t1.Shoes,t2.Shoes, t1.Shirts,t2.Shirts
    from @table1 t1
    JOIN atable2 t2 on t1.Row_ID = t2.Row_ID

I think that should be roughly sensible. Make sure you are using the correct join so the full output for both queries appear

e;fb

Solution 5

You can try following:

SELECT *
FROM (SELECT row_number() over(), * FROM table1) t1
FULL JOIN (SELECT row_number() over(), * FROM table2) t2 using(row_number)

View more solutions