Sorting left join query

Question

I have 3 tables: domains, status, domain_status.

mysql> desc domains;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| domain_id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| domain_url    | varchar(255) | YES  |     | NULL    |                |
| domain_active | int(1)       | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

mysql> desc status;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| status_id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| response_time | varchar(11) | YES  |     | NULL    |                |
| time_pinged   | int(11)     | YES  |     | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+

mysql> desc domain_status;
+------------------+---------+------+-----+---------+----------------+
| Field            | Type    | Null | Key | Default | Extra          |
+------------------+---------+------+-----+---------+----------------+
| domain_status_id | int(11) | NO   | PRI | NULL    | auto_increment |
| domain_id        | int(11) | YES  |     | NULL    |                |
| status_id        | int(11) | YES  |     | NULL    |                |
+------------------+---------+------+-----+---------+----------------+

Data

mysql> SELECT * FROM domains;
    +-----------+----------------+---------------+
    | domain_id | domain_url     | domain_active |
    +-----------+----------------+---------------+
    |         1 | yahoo.com      |             1 |
    |         2 | google.com     |             1 |
    +-----------+----------------+---------------+
    2 rows in set

    mysql> SELECT * FROM status;
    +-----------+---------------+-------------+
    | status_id | response_time | time_pinged |
    +-----------+---------------+-------------+
    |         1 | 143           |  1344373818 |
    |         2 | 0             |  1344373945 |
    |         3 | 40            |  1344374045 |
    |         4 | 504           |  1344375047 |
    +-----------+---------------+-------------+
    4 rows in set

    mysql> SELECT * FROM domain_status;
    +------------------+-----------+-----------+
    | domain_status_id | domain_id | status_id |
    +------------------+-----------+-----------+
    |                1 |         1 |         1 |
    |                2 |         1 |         2 |
    |                3 |         2 |         3 |
    |                4 |         2 |         4 |
    +------------------+-----------+-----------+

I am trying to list the domains and show the record from status with the highest time_pinged row in the status table. I tried the MAX function with a group by but that only works if there is no other column data to be displayed, which is not the case here.

The query right now shows 4 rows, I have been trying for hours now to get it to display 2 rows whilst showing the highest time_pinged

Query:

SELECT
    domains.domain_id,
    domain_url,
    response_time,
    time_pinged,
    domain_active
FROM
    (domains)
LEFT JOIN domain_status ON domain_status.domain_id = domains.domain_id
LEFT JOIN status ON status.status_id = domain_status.status_id
WHERE
    (
        domains.domain_id LIKE '%%'
        OR domain_url LIKE '%%'
        OR response_time LIKE '%%'
        OR time_pinged LIKE '%%'
        OR domain_active LIKE '%%'
    )
ORDER BY
    domain_id ASC
LIMIT 25

Query Result:

+-----------+----------------+---------------+-------------+---------------+
| domain_id | domain_url     | response_time | time_pinged | domain_active |
+-----------+----------------+---------------+-------------+---------------+
|         1 | dhhcontrol.com | 143           |  1344373818 |             1 |
|         1 | dhhcontrol.com | 0             |  1344373945 |             1 |
|         2 | google.com     | 504           |  1344375047 |             1 |
|         2 | google.com     | 40            |  1344374045 |             1 |
+-----------+----------------+---------------+-------------+---------------+
4 rows in set

Sorry if its a bit long winded, I've tried select within joins etc.. and I can't figure it out. Thanks!

Answer 1

I think you need to have an extra subquery that will get their max values and later join them with your tables. Try this one: (INNER JOIN will do this since all domains with the highest ping will only be retrieved)

SELECT  y.domain_id, 
        y.domain_url,
        w.response_time,
        w.time_pinged,
        y.domain_active
FROM    domains y INNER JOIN
            (
                SELECT  a.domain_id, MAX(c.time_pinged) MaxPing
                FROM    domains a 
                            INNER JOIN domain_status b
                                on a.domain_id = b.domain_id
                            INNER JOIN `status` c
                                on b.status_id = c.status_ID
                GROUP BY a.domain_id
            ) z ON  y.domain_id = z.domain_id
            INNER JOIN domain_status x
                ON  y.domain_id = x.domain_id
            INNER JOIN `status` w
                ON  x.status_id = w.status_ID AND
                    z.MaxPing = w.time_pinged
-- WHERE ... your condition
ORDER BY domain_id ASC
LIMIT 25