CODEWITHSUNDEEP
sqlsql-server
Brave Soul
Brave Soul

Reputation: 3620

SQL Server Circular Query

I have 4 tables, in that I want to fetch records from all 4 and aggregate the values

I have these tables

enter image description here

I am expecting this output

enter image description here

but getting this output as a Cartesian product

enter image description here

It is multiplying the expenses and allocation

Here is my query

select 
    a.NAME, b.P_NAME,
    sum(a.DURATION) DURATION,
    sum(b.[EXP]) EXPEN 
from
    (select 
         e.ID, a.P_ID, e.NAME, a.DURATION DURATION 
     from 
         EMPLOYEE e 
     inner join 
         ALLOCATION a ON e.ID = a.E_ID) a
inner join 
    (select 
         p.P_ID, e.E_ID, p.P_NAME, e.amt [EXP] 
     from 
         PROJECT p 
     inner join 
         EXPENSES e ON p.P_ID = e.P_ID) b ON a.ID = b.E_ID 
                                          and a.P_ID = b.P_ID 
group by 
    a.NAME, b.P_NAME

Can anyone suggest something about this.

Upvotes: 0

Views: 937

Answers (3)

Brave Soul
Brave Soul

Reputation: 3620

Hi I got the answer what I want from some modification in the query

The above query is also working like a charm and have done some modification to the original query and got the answer

Just have to group by the inner queries and then join the queries it will then not showing Cartesian product

Here is the updated one

select a.NAME,b.P_NAME,sum(a.DURATION) DURATION,sum(b.[EXP]) EXPEN from

(select e.ID,a.P_ID, e.NAME,sum(a.DURATION) DURATION from EMPLOYEE e inner join ALLOCATION a
ON e.ID=a.E_ID group by e.ID,e.NAME,a.P_ID) a

inner join 

(select p.P_ID,e.E_ID, p.P_NAME,sum(e.amt) [EXP] from PROJECT p inner join EXPENSES e 
ON p.P_ID=e.P_ID group by p.P_ID,p.P_NAME,e.E_ID) b 

ON a.ID=b.e_ID and a.P_ID=b.P_ID group by a.NAME,b.P_NAME

Showing the correct output

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

The following should work:

SELECT e.Name,p.Name,COALESCE(d.Duration,0),COALESCE(exp.Expen,0)
FROM
   Employee e
      CROSS JOIN
   Project p
      LEFT JOIN
   (SELECT E_ID,P_ID,SUM(Duration) as Duration FROM Allocation
    GROUP BY E_ID,P_ID) d
      ON
        e.E_ID = d.E_ID and
        p.P_ID = d.P_ID
      LEFT JOIN
   (SELECT E_ID,P_ID,SUM(AMT) as Expen FROM Expenses
    GROUP BY E_ID,P_ID) exp
      ON
         e.E_ID = exp.E_ID and
         p.P_ID = exp.P_ID
WHERE
    d.E_ID is not null or
    exp.E_ID is not null

I've tried to write a query that will produce results where e.g. there are rows in Expenses but no rows in Allocations (or vice versa) for some particular E_ID,P_ID combination.

Upvotes: 3

Harikaran K
Harikaran K

Reputation: 428

Use left join in select query by passing common id for all table

Upvotes: 0

Related Questions