#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    22
    Rep Power
    0

    Quite simple query, cannot get into logic though


    Hi,

    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:
    **************
    payment_id
    payment_user
    payment_amount
    payment_end_date
    payment_status

    Table B:
    **************
    user_id
    user_name
    user_surname
    user_club


    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.

    Thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Code:
    SELECT users.user_name
         , these.latest
      FROM users
    INNER
      JOIN ( SELECT payment_user
                  , MAX(payment_end_date) AS latest
               FROM user_payments
              WHERE payment_status = 'Active'
             GROUP
                 BY payment_user
             HAVING latest < CURRENT_DATE ) AS these
        ON these.payment_user = users.user_id
     WHERE users.user_club = 'sevenup'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    22
    Rep Power
    0
    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.

    Thanks
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by BlazeMike
    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.
    okay, start with the query inside the parentheses

    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

    helps?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    22
    Rep Power
    0
    Originally Posted by r937
    okay, start with the query inside the parentheses

    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

    helps?
    i think i got it!!!

    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!

IMN logo majestic logo threadwatch logo seochat tools logo