Create UNION SQL

ach8 · Post by **ach8** » Fri Aug 22, 2014 3:52 pm

How to solution show result this sql on PHPMaker ?

SELECT x.FieldA, x.FieldB, x.FieldC, x.FieldD FROM (
SELECT 'Penerimaan' AS FieldA, FieldB, FieldC, SUM(FieldX) AS FieldC FROM t1 GROUP BY FieldB, FieldC
UNION
SELECT 'Pengeluaran' AS FieldA, FieldB, FieldC, SUM(FieldX) AS FieldC FROM t2 GROUP BY FieldB, FieldC
) x
ORDER BY FieldB, FieldC

scs · Post by **scs** » Sat Aug 23, 2014 12:22 am

Create VIEW, then use the VIEW to present the result.

ach8 · Post by **ach8** » Mon Aug 25, 2014 12:25 pm

ERROR : View's SELECT contains a subquery in the FROM clause

mobhar · Post by **mobhar** » Mon Aug 25, 2014 2:18 pm

To solve the issue, try to make two separated Database Views, afterwards make a new Database View which contains the relationship between those two Database Views.

ach8 · Post by **ach8** » Mon Aug 25, 2014 2:53 pm

I have try about make a new database view, but i confuse how to display like my query to relationship between those two my database views?

mobhar · Post by **mobhar** » Mon Aug 25, 2014 4:15 pm

Try this Database View:

SELECT 'Penerimaan' AS FieldA, FieldB, FieldC, SUM(FieldX) AS FieldC FROM t1 GROUP BY FieldB, FieldC
UNION
SELECT 'Pengeluaran' AS FieldA, FieldB, FieldC, SUM(FieldX) AS FieldC FROM t2 GROUP BY FieldB, FieldC