CODEWITHSUNDEEP
sql-serverselect
epx
epx

Reputation: 87

SQL Server SELECT statement

I need a query that returns rows from table A if value X in table A is not the same as the sum of value Y from corresponding row(s) in table B. The issue is that there may or may not be rows in table B that correspond to the rows in table A, but if there no rows in table B, then the rows from table A should still be returned (because there is not a matching value in table B.) So it is like a LEFT OUTER join scenario, but the extra complication of having a comparison as an additional selection criteria.

I have a query that does the opposite, ie. returns rows if the value in table A is the same as the value of row(s) in table B, but sadly this isn't what I need!

SELECT TableA.id, TableA.bdate
FROM TableA
LEFT JOIN TableB ON TableB.ID = TableA.id
WHERE TableA.select_field = 408214
AND TableA.planned_volume = 
(select sum(actual_volume)
 from 
 TableB 
 where TableB.id = TableA.id)
ORDER BY TableA.id

Any help greatly appreciated.

Upvotes: 0

Views: 1119

Answers (3)

onedaywhen
onedaywhen

Reputation: 57023

WITH TotalVolumes
     AS 
     (
      SELECT id, SUM(actual_volume) AS total_volume
        FROM TableB 
       GROUP 
          BY id
     )
SELECT id, bdate, planned_volume 
  FROM TableA
EXCEPT 
SELECT A.id, A.bdate, T.total_volume
  FROM TableA AS A 
       JOIN TotalVolumes AS T 
          ON A.id = T.id;

Upvotes: 0

Justin Pihony
Justin Pihony

Reputation: 67075

How about something like this:

SELECT TableA.Id, TableA.bdate
FROM TableA
    LEFT JOIN 
    (
        SELECT Id, SUM(actual_volume) AS b_volume
        FROM TableB 
        GROUP BY Id
    ) AS TableBGrouping
        ON TableBGrouping.Id= TableA.Id AND TableA.planned_volume <> b_volume
ORDER BY TableA.Id

Upvotes: 1

Sam DeHaan
Sam DeHaan

Reputation: 10325

SELECT TableA.id, TableA.bdate
FROM TableA
LEFT JOIN TableB ON TableB.ID = TableA.id
AND TableA.planned_volume <> 
(select sum(actual_volume)
 from 
 TableB 
 where TableB.id = TableA.id)
ORDER BY TableA.id

Upvotes: 0

Related Questions