Help me to merge two view into one (one table is join but another is using group by)

ASHMITP 141 Reputation points
2023-02-15T06:41:21.08+00:00

Hi All,

Here are my query, please assist me to merge these two view in one

Q1:
 

Select

Table1.C1,

Table2.C2,

Table3.C3

FROM

Table1 join Table2 Join Table3

Q2:

Select

Table4.C1,

Table4.C2,

Sum(C3),

Sum(C4)

FROM

Table4

Group By Table4.C1, Table4.C1

 

How to merge this two view in one common fiels is Table1.C1 (Q1) and Table4.C1 (Q2)

SQL Server | Other
0 comments No comments
{count} votes

Accepted answer
  1. Anonymous
    2023-02-15T07:25:00.3133333+00:00

    Hi @ASHMITP

    First of all, there are a few things in your code that confuse me.

    In Q1. If you want to query C1 in table1, C2 in table2, and C3 in table3, the query statement should look like this.

    Select Table1.C1,Table2.C2,Table3.C3 FROM Table1,Table2,Table3;
    

    In Q2. I guess you want to group by Table4.C1, Table4.C2.

    How to merge this two view in one common fiels is Table1.C1 (Q1) and Table4.C1 (Q2)

    Since the two queries have a common field, I guess it should be using a table join. But I'm not sure which connection you need, inner join or left outer join or something else. I'll use inner joins as a demonstration.

    ;with T1 as(
      Select Table1.C1,Table2.C2,Table3.C3 FROM Table1,Table2,Table3
    ),T2 as(
      Select Table4.C1,Table4.C2,Sum(C3) as sumc3,Sum(C4) as sumc4 FROM Table4 Group By Table4.C1, Table4.C2)
    select * from T1 as A inner join T2 as B on A.C1 = B.C1;
    
    

    Best regards,

    Percy Tang


    If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Olaf Helper 47,441 Reputation points
    2023-02-15T06:51:17.59+00:00

    You can use the UNION (ALL) operator to combine 2 ore more resultsets, but it's mandatory that all have the same count of columns and the data types of each column combination have to match.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.