combining 2 or more sql statements

[abushahin]

hey there! ive got four queries that need combining 1:

SELECT amount, notes FROM credit WHERE customer_Id = $cust_id

2:

SELECT amount, notes FROM payment WHERE customer_Id = $cust_id

3:

SELECT sum( amount ) AS totalpaid FROM payment WHERE customer_Id = $cust_id

4:

SELECT sum( amount ) AS totaltaken FROM credit WHERE customer_Id = $cust_id

im not totally fluent at sql queries but these are the queries that id like to be able to retreive all at once via ine single query instead of the repetition four times, any help appreciated thanks abu ;P

http://abushahin.eu

[Lost User]

Try this:

SELECT amount, notes FROM credit WHERE customer_Id = $cust_id
UNION ALL
SELECT amount, notes FROM payment WHERE customer_Id = $cust_id
UNION ALL
SELECT sum( amount ), '' AS totalpaid FROM payment WHERE customer_Id = $cust_id
UNION ALL
SELECT sum( amount ), '' AS totaltaken FROM credit WHERE customer_Id = $cust_id

[suguimoto]

1, 2: Try this:

SELECT amount, note FROM credit, payment WHERE customer_Id = $cust_id

To join them all, try JOIN command, I really dont remember how it would work for you.

[miss YY]

when you do this,it only union those fields,can not make sum(amount) show as an Independent field.how could we do?

[Arun Immanuel]

SELECT SUM(A.amount) FROM (
SELECT amount, notes FROM credit WHERE customer_Id = $cust_id
UNION ALL
SELECT amount, notes FROM payment WHERE customer_Id = $cust_id
UNION ALL
SELECT sum( amount ), '' AS totalpaid FROM payment WHERE customer_Id = $cust_id
UNION ALL
SELECT sum( amount ), '' AS totaltaken FROM credit WHERE customer_Id = $cust_id
) A

;

Regards, Arun Kumar.A