Landslyde Posted February 9, 2016 Share Posted February 9, 2016 I'm failing miserably at this, but here's what I have: MariaDB [master]> describe quarters; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | qtr | int(11) | NO | PRI | NULL | | +-------+---------+------+-----+---------+-------+ MariaDB [master]> describe history; +--------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------------+------+-----+---------+----------------+ | historyid | int(10) unsigned | NO | PRI | NULL | auto_increment | | amount | float | NO | | NULL | | | subsidy | char(1) | NO | | NULL | | | last_payment | date | NO | | NULL | | | amount_paid | float | NO | | NULL | | | balance | float | NO | | NULL | | | attend | char(1) | NO | | N | | | attend_date | date | NO | | NULL | | | groupid | int(11) unsigned | NO | | NULL | | | clientid | int(10) unsigned | NO | MUL | NULL | | | memberid | int(10) unsigned | NO | MUL | NULL | | +--------------+------------------+------+-----+---------+----------------+ MariaDB [master]> Select clientid, last_payment, amount_paid -> From history -> WHERE YEAR(last_payment) = YEAR(CURDATE()) -> AND memberid = "1"; +----------+--------------+-------------+ | clientid | last_payment | amount_paid | +----------+--------------+-------------+ | 3 | 2016-01-26 | 100 | | 3 | 2016-10-29 | 15 | | 3 | 2016-01-30 | 15 | | 4 | 2016-01-26 | 30 | | 4 | 2016-10-29 | 30 | | 4 | 2016-01-30 | 15 | | 1 | 2016-01-26 | 15 | | 1 | 2016-10-29 | 30 | | 1 | 2016-01-30 | 100 | | 2 | 2016-01-26 | 30 | | 2 | 2016-10-29 | 30 | | 2 | 2016-01-30 | 30 | | 3 | 2016-01-28 | 100 | | 4 | 2016-01-30 | 15 | | 1 | 2016-01-30 | 100 | | 2 | 2016-01-30 | 30 | | 5 | 2016-01-29 | 30 | | 5 | 2016-02-02 | 30 | | 3 | 2016-02-02 | 15 | | 4 | 2016-02-02 | 30 | | 1 | 2016-02-02 | 15 | | 2 | 2016-02-02 | 30 | +----------+--------------+-------------+ There's returnable data from `amount_paid`, but the following query returns 0 rows. MariaDB [master]> SELECT qtr AS Quarter, SUM(amount_paid) as Total -> FROM quarters q -> LEFT JOIN history h ON qtr = QUARTER(last_payment) -> WHERE YEAR(last_payment) = YEAR(CURDATE()) -> AND memberid = "1" -> GROUP BY qtr; Empty set (0.01 sec) For the love of Mary! What am I doing wrong? Quote Link to comment Share on other sites More sharing options...
Barand Posted February 9, 2016 Share Posted February 9, 2016 Do you have a record for each quarter in the quarters table? And as you are left joining to the history table, put those conditions in the ON clause instead of the WHERE clause (ie change "WHERE" to "AND" in your query) Quote Link to comment Share on other sites More sharing options...
Landslyde Posted February 9, 2016 Author Share Posted February 9, 2016 (edited) Barand: Truth is, another person helped me with this. The query, the quarters table, all from him. But that was some time back and I can't recall who it was or what forum it was on for me to go back and review my history. This use to work, but somehow I've fouled it up. There will be many members, so having the quarters table store quarterly info isn't an option. Each member must be able to see their own quarterly earnings. As you suggested, I changed the query to: $stmt = $db->prepare('SELECT qtr AS Quarter, SUM(amount_paid) as Total FROM quarters AS q LEFT JOIN history AS h ON YEAR(last_payment) = YEAR(CURDATE()) WHERE qtr = QUARTER(last_payment) AND memberid = :memberid GROUP BY qtr'); $stmt->bindValue(':memberid', $_SESSION["memberid"], PDO::PARAM_INT); but this provided nothing. Even the LEFT JOIN's ON clause looks strange to me. If you decide to help me further, would you also briefly explain the query? I have almost 50 working pages full of queries a lot larger than this, all done by me, but this one...this one's kicking me good. I just don't get it. Thanks. Edited February 9, 2016 by Landslyde Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted February 9, 2016 Solution Share Posted February 9, 2016 my data mysql> SELECT * FROM history; +-----------+----------+--------------+-------------+ | historyid | memberid | last_payment | amount_paid | +-----------+----------+--------------+-------------+ | 1 | 1 | 2015-12-20 | 1000.00 | | 2 | 1 | 2016-01-01 | 150.00 | | 3 | 1 | 2016-01-10 | 50.00 | | 4 | 1 | 2016-02-03 | 60.00 | | 5 | 1 | 2016-02-25 | 40.00 | | 6 | 1 | 2016-05-30 | 300.00 | | 7 | 1 | 2016-06-22 | 200.00 | | 8 | 2 | 2016-01-05 | 155.00 | | 9 | 2 | 2016-02-14 | 205.00 | +-----------+----------+--------------+-------------+ mysql> SELECT * FROM quarters; +-----+ | qtr | +-----+ | 1 | | 2 | | 3 | | 4 | +-----+ the query and results - I told you to put the conditions on the history table into the ON clause, not in the WHERE clause. mysql> SELECT qtr AS Quarter, SUM(amount_paid) as Total -> FROM quarters q -> LEFT JOIN history h ON qtr = QUARTER(last_payment) -> AND YEAR(last_payment) = YEAR(CURDATE()) -> AND memberid = '1' -> GROUP BY qtr; +---------+--------+ | Quarter | Total | +---------+--------+ | 1 | 300.00 | | 2 | 500.00 | | 3 | NULL | | 4 | NULL | +---------+--------+ If you have them in the WHERE clause then the LEFT JOIN behaves as as an INNER JOIN like this mysql> SELECT qtr AS Quarter, SUM(amount_paid) as Total -> FROM quarters q -> LEFT JOIN history h ON qtr = QUARTER(last_payment) -> WHERE YEAR(last_payment) = YEAR(CURDATE()) -> AND memberid = '1' -> GROUP BY qtr; +---------+--------+ | Quarter | Total | +---------+--------+ | 1 | 300.00 | | 2 | 500.00 | +---------+--------+ 1 Quote Link to comment Share on other sites More sharing options...
Landslyde Posted February 9, 2016 Author Share Posted February 9, 2016 I appreciate you, Barand. Every time I think I have a good grasp of the LEFT JOIN, something like this shows me I'm still on a slippery slope. I understand your explanation. And even more than the working solution you provided, the explanation was what I was looking for. Something more to help me try to solidify LEFT JOINS. Thank you for taking time to offer that. It helped. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.