Skip to main content

Union with Group by is possible?

  • November 29, 2018
  • 2 replies
  • 12 views

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

2 replies

DaveT
Forum|alt.badge.img
  • Participating Frequently
  • November 30, 2018

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;


  • New Participant
  • December 14, 2018

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 ;