Multi-Join Query Returns Too Many Rows 1

[mal55]

I am trying to avoid multiple queries / putting data into temp tables and so am trying to write a query that will do two different sums off of the same table in one query.

select
s.name,
sum(t1.Quantity),
sum(t2.Quantity)
from
SecurityMaster s
inner join Trades t1 on s.SecurityID = t1.SecurityID
inner join Trades t2 on s.SecurityID = t2.SecurityID
and t2.value < 100
group by
s.name

My expectation was that for each row in the SecurityMaster table there would be a one-to-one relationship to each of the Trades tables. The problem is that the items in the Trades table are getting read multiple times per loop and the sums are not correct (way too high by a factor of 16).

Any advice?

 

[IanWaterman]

From what you are saying you have not defined a one to many or one to one relationship between Trades and SecurityMaster. YOu must have a many to many which is causing your duplications.

Ian

 

[mal55]

Thanks for your thoughts. There is a one-to-many relationship between the SecurityMaster and the Trades table(the Trades table being the many). But what the query seems to be doing is relating the two Trades tables to one another and I can't see how that should be given that the Trades tables are only related to the SecurityMaster.

Stated another way, is there a way to have a one to many relationship between the SecurityMaster tables and each of the Trades tables?

 

[r937]

SELECT s.name
     , SUM(t.Quantity)                 AS t1.sum
     , SUM(CASE WHEN t.value < 100
                THEN t.Quantity END )  AS t2.sum
  FROM SecurityMaster AS s
INNER
  JOIN Trades AS t 
    ON t.SecurityID = s.SecurityID
GROUP 
    BY s.name

r937.com | rudy.ca

 

[mal55]

That solution has the merit of being correct, efficient and brilliant. Thanks!!!