Group on multiple joins in a view,

1

Hi all,
I have got a very complicated view that I need to apply additional two join to it and also group it only by 3 columns of over twenty main columns the view returns.
I have applied a sub query before my new joins and tried to group it by the sub query select ( 3 columns ) but I cant get it working , is there any roles that stopping this condition, I appreciate any help, fairly beginner here. thanks

2

It is hard to offer any useful suggestions without seeing the code. If the view is very long/complicated, you might construct a simplified example and post it, along with the desired changes you need.

You can wrap the existing query in a CTE and then do the joins and grouping to the CTE, if that makes it simpler conceptually. For example,

;WITH cte AS
(
	--- Your original select query from the view here.
)
SELECT
	c.Col1,
	c.Col2,
	t1.Col3,
	SUM(t2.Col5)
FROM
	cte c
	INNER JOIN Table1 t1 ON t1.col7=c.col1
	INNER JOIN Table2 t2 ON t2.col8=c.col2
GROUP BY
	c.Col1,
	c.Col2,
	t1.Col3;
3

Agree, it is definitely hard to answer without an example, not sure about company policy but it is something like below,
select
w.col1
r.col2
w.col3
c.col4
p.col5
w.col6
l.col7
etc....
my new columns
t.col18
w.col19
t.col20

from w
left outer join cs on w.id=cs.id and w.si=cs.id
left outer join pc on w.pl = pc.cu
left outer join pa on w.pl = pa.pla and w.no=pa.no and w.id = pa.id

left outer join (select l.la, l.ld, l.res from lcr join l.l on la.l
where la.sy = "xx"

left outer join
xxxxxxx
left outer join
xxxxxx

another subquery
another subquery
join
join
join

where w.col1 = "vv"
and w.col2 = 0

I hope I could drew a better picture with above example sorry if that is a little messy
so What I want to do is to add another two joins as below

  1. left outer join t on t.tid = w.id and t.xx = w.xx
  2. and group it by my above three new columns ( t.col18 w.col19 t.col20)
    I hope this make sense
4

any advice ?

5

this is old question, but thanks . I have another issue now which is more complicated , happy to share if you have som free time to help