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

Question

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 |

What error are you getting? Also please post some sample data from the notes and category table as well as an expected output please. — Matt
– Matt, Commented Mar 14, 2019 at 10:30
No error. But the query return all data (cash out and cash in). I only want to get cash in — May Tin
– May Tin, Commented Mar 14, 2019 at 10:36

Nikhil Sahu · Accepted Answer · 2019-03-14 11:56:01Z

1

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.

answered Mar 14, 2019 at 11:56

Nikhil Sahu

2,6412 gold badges36 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matt · Accepted Answer · 2019-03-14 10:48:11Z

Possibly a typo with cash_in and in.

$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 = 'in')
            -
            (SELECT SUM(noteAmount)
             FROM notes
             WHERE DATE_FORMAT(noteDate, '%d-%m-%Y') = DATE_FORMAT(note1.noteDate, '%d-%m-%Y') AND noteType = '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 = 'in'
            ORDER BY note1.noteDate DESC
            LIMIT $start, $per_page
          ")->result();

Antony · Accepted Answer · 2019-03-14 11:37:01Z

0

I believe you are missing a where on your JOIN statement. If this went on the ON element or within the SELECT itself I believe that would fix your problem.

answered Mar 14, 2019 at 11:37

Antony

4,4421 gold badge34 silver badges43 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related