1

There are these tables:

1. PackageQuotas
(PackageId, QuotaId,QuotaValue)
2.Packages
(PackageId, ParentPackageId,..)

I want for a specific ParentPackageID to gather all QuotaValues from PackageQuota (EXCEPT this ParentPackageId) and store the result to a variable.

//Get all packageIds under the parentPackageId
SELECT * FROM Packages WHERE ParentPackageID = 32455 

//Get the quota value for a specific packageId
SELECT * FROM PackageQuotas WHERE QuotaID = @QuotaID AND PackageID =xxx   

//Example with results

**Packages**
32455 NULL
32456 32455
32457 32455
32458 32455

**PackageQuotas**
32455   1801    8
32456   1801    2
32457   1801    1
32458   1801    3

Expected result is : sum = 6 (and not 14)
where 32455 is the actual parentPackageId

Do I have to use a cursor and which is the best way?

Giannis Grivas
  • 3,374
  • 1
  • 18
  • 38
  • 1
    Please edit your question and provide sample data and desired results. It is not clear what you are trying to store in a variable. – Gordon Linoff Dec 09 '15 at 12:48
  • Please google and learn about the GROUP BY clause and SUM() function in SQL. This question does not show any research effort. – Tab Alleman Dec 09 '15 at 13:25

2 Answers2

1

You can try the inner query approach

SELECT SUM(QuotaValue) 
   FROM PackageQuotas 
WHERE QuotaID = @QuotaID 
   AND PackageID IN 
    (
     SELECT PackageId FROM Packages WHERE ParentPackageID = 32455 
     )
DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
  • Sub queries in the WHERE clause can be very slow, compared to joins. This is because the sub query has to be run once for each row returned by the main query. A JOIN on the other hand is only executed once. For more see: http://stackoverflow.com/questions/141278/subqueries-vs-joins – David Rushton Dec 09 '15 at 13:42
  • 3
    @destination-data - in current solution subquery is not correlated and will run once, thereby no performance penalty. – Arvo Dec 09 '15 at 13:47
  • Good point, you example isn't using a value from the main query. Looking at few sample execution plans in SSMS further backs your point up. – David Rushton Dec 09 '15 at 13:59
  • The result is the desired one but do you suggest that way or not in the manner of performance?. – Giannis Grivas Dec 09 '15 at 14:01
  • 1
    As Arvo excellently pointed out there is no performance loss/gain in this example (kudos). As a general rule I favour joins because I prefer to see the relationships between tables laid out in one section of the query. This helps me visualise what is happening. But as is often the case, there is no one answer. – David Rushton Dec 09 '15 at 14:29
  • @GiannisGrivas The above query does not hamper performance as the inner query will only be evaluated once. – DhruvJoshi Dec 11 '15 at 05:27
-1

You could JOIN your two tables. Below is an example. If you are not familiar with this technique it is very much worth your time reading up. JOINs are one of the most powerful, and basic, features of SQL.

I've also included the syntax for capturing the result into a variable. But be careful, if you query returns more than 1 record the variable will only hold the value from the last record.

DECLARE @Result INT;

SELECT
    @Result = SUM(PackageQuotas.QuotaValue)
FROM
    PackageQuotas
        INNER JOIN Packages  ON Packages.PackageId = PackageQuotas.PackageId 
WHERE
  Packages.ParentPackageId = 32455 
;
David Rushton
  • 4,915
  • 1
  • 17
  • 31