Sum() in empty table doesn't return zero

[kolorasta]

i have a query with this basic structure

SELECT id FROM table1,(SELECT SUM(field2) as total2 FROM table2) - (SELECT SUM(field3) as total3 FROM table3) as total

to understand better let's say that:
table1 are clients
table2 is money that enters for clients
table3 is money that exits from clients

i want to obtain a balance. the problem is that if table2 or table3 has no records for certain client, this client is exluded from results

any ideas?

i tried using ISNULL like this:

SELECT id FROM table1,ISNULL((SELECT SUM(field2) as total2 FROM table2),0) - ISNULL((SELECT SUM(field3) as total3 FROM table3),0) as total

but it didn't worked
this is the query

SELECT     id, UPPER(apellido_titular) + ', ' + nombre_titular AS padre,
                          (SELECT     SUM(facturas_items.importe) AS totalf
                            FROM          facturas INNER JOIN
                                                   padres ON facturas.id_padre = padres.id LEFT OUTER JOIN
                                                   facturas_items ON facturas.id = facturas_items.id_documento
                            WHERE      (padres.id = p.id) AND (facturas.fecha_vencimiento < GETDATE())) -
                          (SELECT     SUM(recibos_items.importe) AS totalr
                            FROM          padres INNER JOIN
                                                   recibos ON padres.id = recibos.id_padre LEFT OUTER JOIN
                                                   recibos_items ON recibos.id = recibos_items.id_recibo
                            WHERE      (padres.id = p.id)) AS total
FROM         padres p
WHERE     (activo = 1) AND
                          ((SELECT     SUM(facturas_items.importe) AS totalf
                              FROM         facturas INNER JOIN
                                                    padres ON facturas.id_padre = padres.id LEFT OUTER JOIN
                                                    facturas_items ON facturas.id = facturas_items.id_documento
                              WHERE     (padres.id = p.id) AND (facturas.fecha_vencimiento < GETDATE())) -
                          (SELECT     SUM(recibos_items.importe) AS totalr
                            FROM          padres INNER JOIN
                                                   recibos ON padres.id = recibos.id_padre LEFT OUTER JOIN
                                                   recibos_items ON recibos.id = recibos_items.id_recibo
                            WHERE      (padres.id = p.id)) > 0)
ORDER BY UPPER(apellido_titular) + ', ' + nombre_titular

sorry for my poor english

 

[WayneRyan]

K,

Wow, are those queries for JET?

Access is woefully slow with Nested Selects. I just had to say that.

Anyway, the NZ function can be used to force a value of 0 when the data is Null.

Wayne

 

[kolorasta]

sorry I didn't mention that it's an access project (SQL Server)

 

[WayneRyan]

K,

OK, that's better, JET would have a very tough time with that.

I think you can just change your INNER JOINs to LEFT JOINs, that will
get all of your data.

Then, you can use the Coalesce function to change any Null elements
to 0. This will give the Sum function something to work with.

hth,
Wayne