CODEWITHSUNDEEP
mysqlsqljoingroup-by
PeakGen
PeakGen

Reputation: 23025

`Group By` returns empty rows

Please have a look at the below code.

SELECT ongoing_portfolio.*,
    Portfolio.Activation,
    SUM(case when Transaction_TimeStamp <= ongoing_portfolio.`Updated_Date` 
        then Transactions.`Transaction_Amount` ELSE 0 end) AS `Total` 
    FROM Ongoing_Portfolio
    INNER JOIN Portfolio ON Ongoing_Portfolio.idPortfolio = Portfolio.idPortfolio
    INNER JOIN Transactions ON Transactions.`idPortfolio` = Ongoing_Portfolio.idPortfolio
    WHERE ongoing_portfolio.`idPortfolio`= 5        
    GROUP BY Ongoing_Portfolio.`Updated_Date`  DESC LIMIT 4

This works fine "only" if there is "at least" 1 transaction for the particular portfolio. If there are no transactions at all for that portfolio, then it simply returns empty row, which means nothing.

I tried grouping with Ongoing_Portfolio.idOngoing_Portfolio, Transactions.idTransactions, Ongoing_Portfolio.idPortfolio but no good at all. Why this is happening like this?

Upvotes: 0

Views: 69

Answers (2)

f1zz0_13
f1zz0_13

Reputation: 495

Replace 

 INNER JOIN Transactions ON Transactions.`idPortfolio` = Ongoing_Portfolio.idPortfolio

by

 LEFT JOIN Transactions ON Ongoing_Portfolio.idPortfolio = Transactions.`idPortfolio`

Upvotes: 1

diemacht
diemacht

Reputation: 2102

Try using LEFT JOIN instead of INNER JOIN

Upvotes: 0

Related Questions