I have 4 tables. Table1 contains user information, Table2 contains actual orders, Table3 contains future orders and Table4 contains all invoices that the user has accumulated. Table 2, Table3 and Table4 will have different data having UserId as a common field. I'll need to do an outer join to get the total count for a user my resultset needs to look something like this:
Username Orders Placed Orders Paid Invoices
John Doe 20 30 24
Jane Doe 5 25 10
John Smith 0 10 0

All tables have different data but I need to get the counts for all tables for each user. What is the best way to get this done?

SELECT a.CUSTID, b.ORDERSCOUNT, c.PENDINGCOUNT, d.PAIDCONT
FROM TABLE1 a
LEFT OUTER JOIN 
(
 SELECT CUSTID, COUNT(ORDER) as ORDERSCOUNT
 FROM TABLE2
 GROUP BY CUSTID
) b
ON a.CUSTID = b.CUSTID

LEFT OUTER JOIN 
(
 SELECT CUSTID, COUNT(ORDER) as PENDINGCOUNT
 FROM TABLE2
 GROUP BY CUSTID
) c
ON a.CUSTID = c.CUSTID

LEFT OUTER JOIN 
(
 SELECT CUSTID, COUNT(ORDER) as PAIDCOUNT
 FROM TABLE2
 WHERE PAIDFLAG = 1
 GROUP BY CUSTID
) d
ON a.CUSTID = d.CUSTID