Union with Group by is possible?

tahsin18

I have two tables like this -

Nu

i j
1 2
1 3
1 4

Nv

i j
2 1
2 5
3 1
3 6

What I want to do is I need to find (Select j From Nu UNION Select j from Nv) for every Nu.i , Nv.i. Like this -

Nu.i Nv.i v
1 2 2
1 2 3
1 2 4
1 2 1
1 2 5
1 3 2
1 3 3
1 3 4
1 3 1
1 3 6

Is there any way I can query this with Vertica SQL?

I tried -
Select Nu.i, Nv.i, (Select j from Nu UNION Select j from Nv group by Nv.j) as v from Nu, Nv;

Error: ERROR 4840: Subquery used as an expression returned more than one row
and
Select Nu.i, Nv.i, (Select j from Nu UNION Select j from Nv) as v from Nu, Nv group by Nu.i, Nv.i;
Error: Subqueries in the SELECT or ORDER BY are not supported if the subquery is not part of the GROUP BY

Please, let me know you suggestion

DaveT

I am probably not totally sure what you want to do but here is an idea.

You have a select clause within another select clause which means only a single constant value should come out of the inner select clause. Otherwise you are trying to perform a join within the select clause.

You could write it like the following but I don't think that is what you want want because it will cross join every combination whereas you seem to only want the rows where j is associated with either the Nu.i or the Nv.i

select distinct Nu.i, Nv.i, v.j from Nu, Nv, (select j from Nu union select j from Nv) v;

So, the above query gets you 2 extra rows you apparently don't want:
1 2 6
1 3 5

Perhaps something like the following will work for you. I am not sure if this is the most efficient way but I think it gets you the values you want:

select distinct Nu.i, Nv.i, Nu.j from Nu cross join Nv union select distinct Nu.i, Nv.i, Nv.j v from Nu cross join Nv;

Lokesh

You will get all combinations with this but can not guarantee ordering.

select * from (select distinct Nu.i, Nv.i from Nu cross join Nv) N_uv cross join (select j from Nu union select j from Nv) Nw ;