CODEWITHSUNDEEP
mysql
Unexpected Pair of Colons
Unexpected Pair of Colons

Reputation: 769

Return max join result only if ID matches

I'm attempting to build a query that will select all tenants of a rental property. Each property can have multiple tenants, and each tenant can be moved to a different property.

Here's what (part of) the user property table looks like:

User ID  |  Property ID  |  Move In Date  |  Move Out Date
----------------------------------------------------------
224      |  33A          |  2015-11-01    |  NULL
224      |  36B          |  2015-11-15    |  NULL
226      |  33A          |  2015-11-01    |  NULL

In the example above User 224 moved into property 33A on the 1st, then property 36B on the 15th. For this issue, and example, I'm assuming that the move_out_date column did not get properly populated with a date. In this instance I need a query that will bypass that issue.

Here's what I have for my query so far:

SELECT
    u.user_email, p.user_fname, p.user_lname, r.move_in_date AS occupant_date
FROM
    user u
LEFT JOIN
    user_profile p ON p.user_id = u.user_id
LEFT JOIN
    (
        SELECT
            user_id, property_id, move_in_date
        FROM
            user_property
        WHERE
            move_out_date IS NULL
        ORDER BY
            move_in_date DESC
    ) r ON r.user_id = u.user_id
WHERE
    r.property_id = '33A'
GROUP BY
    u.user_id
ORDER BY
    r.move_in_date ASC, p.user_fname ASC

This query returns each occupants info, and move in date. For property 33A it is returning Users 224 and 226, but I only want to return User 226 because technically User 224 moved from that property.

Currently the output table looks like this:

User ID  |  Property ID  |  Move In Date  |  Move Out Date
----------------------------------------------------------
224      |  33A          |  2015-11-01    |  NULL
226      |  33A          |  2015-11-01    |  NULL

User ID  |  Email        |  First Name  |  Last Name  | Occupant Date
---------------------------------------------------------------------
224      |  [email protected]  |  Kevin       |  Doe        |  2015-11-01
226      |  [email protected]  |  Tom         |  Smith      |  2015-11-01

But I'd only like the results for User 226 specifically for property ID 33A. Ultimately, this...

User ID  |  Email        |  First Name  |  Last Name  | Occupant Date
---------------------------------------------------------------------
226      |  [email protected]  |  Tom         |  Smith      |  2015-11-01

Is this possible to do in one query, or will I need to make a couple of them? I appreciate any help on this!

Upvotes: 2

Views: 49

Answers (1)

JRD
JRD

Reputation: 1987

One way for getting the current tenant for a given property:

select p.user_id, p.property_id, p.move_in_date from
user_property p
join (
    -- Get users most recent move in date.
    select max(move_in_date) move_in_date, user_id
    from   user_property
    group by user_id
) u_move_in on (p.user_id = u_move_in.user_id and p.move_in_date = u_move_in.move_in_date)
join (
    -- Get the property's most recent move in date.
    select max(move_in_date) move_in_date, property_id
    from   user_property
    where  property_id = '33A'
    group by property_id
) p_move_in on (p.property_id = p_move_in.property_id and p.move_in_date = p_move_in.move_in_date);

Find the user(s) with most recent move in date = property most recent move in date.

Upvotes: 2

Related Questions