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