MySQL Forums
Forum List  »  InnoDB

Re: Using UNION to add selected field in multiple select statements?
Posted by: Don Ireland
Date: December 29, 2015 09:37PM

Never mind. After posting, I continued to ponder this and realized that a union won't work. But a JOIN on the other hand MIGHT. So I tried it. With this code, each individual select statement becomes a field in the main select statement.

SELECT (
aSplit.amount + a.amount - tSplit.amount - t.amount
)
FROM (
(

SELECT sum( a.amount ) AS amount
FROM `transactions` AS p
INNER JOIN `transactions` AS a
INNER JOIN `parents` AS r ON ( r.parentID = p.id
AND r.childID = a.id )
WHERE p.userID =4
AND p.acctID =1
) AS aSplit
JOIN (

SELECT sum( amount ) AS amount
FROM `transactions`
WHERE userID =4
AND acctID =1
) AS a
JOIN (

SELECT sum( a.amount ) AS amount
FROM `transactions` AS p
INNER JOIN `transactions` AS a
INNER JOIN `parents` AS r ON ( r.parentID = p.id
AND r.childID = a.id )
WHERE p.userID =4
AND a.catID =1
AND a.type = 'transfer'
) AS tSplit
JOIN (

SELECT sum( amount ) AS amount
FROM `transactions`
WHERE userID =4
AND catID =1
AND TYPE = 'transfer'
) AS t)

Don

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Using UNION to add selected field in multiple select statements?
624
December 29, 2015 09:37PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.