CODEWITHSUNDEEP
mysqljoin
Azizi Musa
Azizi Musa

Reputation: 1029

mysql multiple query with JOIN table and merge result to 1 row

SELECT save AS 'Simpanan', owe AS 'Hutang'
FROM
(
SELECT t.amount AS save, NULL AS owe
FROM trans t JOIN category cat
ON t.category_id = cat.category_id
WHERE cat.category_id = 1
UNION ALL
SELECT NULL AS save, t.amount AS owe
FROM trans t JOIN category cat
ON t.category_id = cat.category_id
WHERE cat.category_id = 2
)

I've been struggling with this mysql query. What I wanted to achieve is that, the result become only 1 row. Currently this query output 2 row with some field in column are NULL. How can I make it to only output 1 row like this : 

Simpanan    Hutang  
500         200

Take a look here : http://sqlfiddle.com/#!5/89973/30

Upvotes: 0

Views: 48

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175726

If you are sure about only one result use subquery.

SELECT 
   (SELECT t.amount AS save
    FROM trans t JOIN category cat
    ON t.category_id = cat.category_id
    WHERE cat.category_id = 1) AS 'Simpanan',
    (SELECT t.amount AS owe
    FROM trans t JOIN category cat
    ON t.category_id = cat.category_id
    WHERE cat.category_id = 2) AS 'Hutang'

Or:

SELECT MAX(save) AS 'Simpanan', MAX(owe) AS 'Hutang'
FROM
(
SELECT t.amount AS save, NULL AS owe
FROM trans t JOIN category cat
ON t.category_id = cat.category_id
WHERE cat.category_id = 1
UNION ALL
SELECT NULL AS save, t.amount AS owe
FROM trans t JOIN category cat
ON t.category_id = cat.category_id
WHERE cat.category_id = 2
)

Upvotes: 2

Related Questions