You can't do it as one query without a lot of redundant data, but you can do it with two.
First off, you'd probably be better reorganising your tables so that for each 'transaction' there's a 'credit' and 'debit' (I'm assuming your payment and credit tables are opposites of each other, despite credit and payment being the same!). This means you can easily sum the amount debited and credited using a single query, e.g.
Code: Select all
SELECT SUM(credit) AS credited, SUM(debit) AS debited FROM tbl_transactions WHERE customer_id=123;
But, to answer your question, you can join the first two queries using a UNION, but you'll need an additional column to identify whether its payment or credit, or alternatively invert one of the numbers (i.e., a negative value indicates a debit), so:
Code: Select all
SELECT amount, notes, 'credit' as source FROM credit WHERE customer_Id = $cust_id UNION SELECT amount, notes, 'payment' as source FROM payment WHERE customer_Id = $cust_id;
Similarly you could UNION together the last two queries (return two rows) or as subqueries (single row but two columns), but I'm not really sure there'd be any benefit to doing so as two queries are still being carried out.