CodeIgniter MySQL query with multiple subqueries on the same table is not filtering records as expected

I have a MySQL query in CodeIgniter like this:

$report = $this->db->query("
    SELECT
        c.categoryName,
        note1.*,
        (
            (
                SELECT SUM(noteAmount)
                FROM notes
                WHERE
                    DATE_FORMAT(noteDate, '%d-%m-%Y') = DATE_FORMAT(note1.noteDate, '%d-%m-%Y')
                    AND noteType = 'cash_in'
            )
            -
            (
                SELECT SUM(noteAmount)
                FROM notes
                WHERE
                    DATE_FORMAT(noteDate, '%d-%m-%Y') = DATE_FORMAT(note1.noteDate, '%d-%m-%Y')
                    AND noteType = 'cash_out'
            )
        ) as trxCount
    FROM
        notes AS note1
    JOIN
        (
            SELECT noteDate
            FROM notes
            GROUP BY noteDate
            HAVING COUNT(noteDate) > 0
        ) AS note2
        ON note1.noteDate = note2.noteDate
    JOIN category c
        ON c.categoryID = note1.categoryID
    WHERE note1.noteType = 'cash_in'
    ORDER BY note1.noteDate DESC
    LIMIT $start, $per_page
")->result();

See on WHERE Clause:

WHERE note1.noteType = 'cash_in'

I only want to take the cash data, but why do I get all the data (including cash out and others)?

My table also has cash out data.

notes table:

category table:

| categoryID | categoryName | parentID |

Solution

The query

SELECT noteDate
          FROM notes
          GROUP BY noteDate
          HAVING COUNT(noteDate) > 0

is selecting the entire notes tables as note2 (including cash_out entries) and filtering is being done only on entries where note1.noteType = 'cash_in'. This will give both cash_in and cash_out entries as the join condition only checks for note1.noteDate = note2.noteDate which will always have some match.

Probably add where condition inside the on condition and see if that is what you want.