March 22nd, 2013, 03:02 PM
Quite simple query, cannot get into logic though
Ive been struggling all day and cannot get into the logic of how to do this even though for somebody who knows mysql well i think its easy.
I have two tables.
Table A has payment Entries. Table B is users table. A user can have multiple entries in table A.
What i want is to have a query that will show me the users whom last payment date is past. (so it has to be ordered DESC)
Basically i need the following two queries to become one but cannot think of a way to do it.
SELECT * FROM users WHERE user_club="sevenup" ORDER BY user_name ASC
SELECT * FROM user_payments WHERE payment_user ='$user' AND payment_status = 'Active' AND payment_end_date < '$dgdate' GROUP BY payment_user ORDER BY payment_id DESC
Description: Find all users who belong to club "Sevenup". Find the last payment for each of them. If their last payment has end_date older than current day return their name.
any help apreciated.
March 22nd, 2013, 04:03 PM
JOIN ( SELECT payment_user
, MAX(payment_end_date) AS latest
WHERE payment_status = 'Active'
HAVING latest < CURRENT_DATE ) AS these
ON these.payment_user = users.user_id
WHERE users.user_club = 'sevenup'
March 22nd, 2013, 05:05 PM
wow thanks, works great.
I must study more about JOINS, i was never able to understand them. Ive seen so many examples but yet havent got the logic on when and how to use them
i understand a basic join query, the select query inside the inner join is what gets me confused
i would really appreciate it if you could split the query in parts and descripe what each part does. If i understand it then i may get the logic.
If you cant then its ok.
March 22nd, 2013, 07:14 PM
okay, start with the query inside the parentheses
Originally Posted by BlazeMike
a subquery like this, in the FROM clause, is called a derived table or inline view -- both names accurately describe how it works, by producing a result set that acts as though it were a table
the subquery uses a GROUP BY to obtain the latest active payment date for each user, and the HAVING clause ensures that only users with a past payment are returned
then the join to the user table allows the query to return the user's name for the specified club
March 23rd, 2013, 03:05 AM
i think i got it!!!
Originally Posted by r937
i was getting confused by the "these.latest" that was used because it was not an actual table column but then i realized that this "These" was referring to the whole subquery and "latest" to the sum inside that query.
I think i now understand it, thank you very much!