CODEWITHSUNDEEP
mysql
SISYN
SISYN

Reputation: 2269

Combined MySQL query help - 2 tables

I have a table rosters and a table members. They are setup like this:

rosters table
id     team     member
 1     1        1
 2     1        2
 3     1        3

members table
id     name     last_active
 1     Dan      1454815000
 2     Ewan     1454817500
 3     Cate     1454818369

I need to fetch all rows in rosters where team=1. Then, I need to take all those returned results and use the member column to fetch the largest last_active value from members where id is in that list of results returned from rosters

This is how I would do it in PHP, but I'm sure there's a way to just use a more efficient query.

$rosterList = $db->query('SELECT * FROM rosters WHERE team=1');
$lastActive = 0;
foreach($rosterList as $roster) {
    $activity = $db->query('SELECT last_active FROM members WHERE id='.$roster['team']);
    if ( $activity > $lastActive )
        $lastActive = $activity;
}

if ( $lastActive > time()-60 )
    echo 'team is currently online';

It would be nice if it could just return one result with the latest last_active column but if it returns all matches in the members table that would be fine too.

Upvotes: 0

Views: 71

Answers (3)

Smit Patel
Smit Patel

Reputation: 177

You can use following solution: 

$result_array = mysql_query("SELECT * FROM rosters as r INNER JOIN members as m on r.member=m.id AND r.team = '1' ORDER BY last_active DESC LIMIT 1");

$lastActive = 0;

if($result_array)
{
    while($row = mysql_fetch_row($result_array,MYSQL_ASSOC))
    {
        $lastActive = $result_array['last_active'];
    }
}       

if ( !empty($lastActive) && $lastActive > time()-60 )
    echo 'team is currently online';

Upvotes: 1

Paul Maxwell
Paul Maxwell

Reputation: 35593

By using an ORDER BY (descending) on that last_actie column, then limiting to just 1 row, you get access to the whole member row.

sqlfiddle demo

MySQL 5.6 Schema Setup:

CREATE TABLE members
    (`id` int, `name` varchar(4), `last_active` int)
;

INSERT INTO members
    (`id`, `name`, `last_active`)
VALUES
    (1, 'Dan', 1454815000),
    (2, 'Ewan', 1454817500),
    (3, 'Cate', 1454818369)
;

CREATE TABLE rosters
    (`id` int, `team` int, `member` int)
;

INSERT INTO rosters
    (`id`, `team`, `member`)
VALUES
    (1, 1, 1),
    (2, 1, 2),
    (3, 1, 3)
;

Query 1:

select
    m.*
from members m
join rosters r on m.id = r.member
where r.team = 1
order by m.last_active DESC
limit 1

Results:

| id | name | last_active |
|----|------|-------------|
|  3 | Cate |  1454818369 |

Upvotes: 2

spencer7593
spencer7593

Reputation: 108410

I'm a little confused why you are retrieving rows from members where the id value matches the team value from rosters. Seems like you would want to match on the member column.

You can use a join operation and an aggregate function. To get the largest value of last_active for all members of a given team, using the member column, something like this:

  SELECT MAX(m.last_active) AS last_active
    FROM members m
    JOIN rosters r
      ON r.member = m.id
   WHERE r.team = 1

To do the equivalent of the original example, using the team column (and again, I don't understand why you would do this, because it doesn't look right): 

SELECT MAX(m.last_active) AS last_active
  FROM members m
  JOIN rosters r
    ON r.team = m.id
 WHERE r.team = 1

The MAX() aggregate function in the SELECT list, with no GROUP BY clause, causes all of the rows returned to be "collapsed" into a single row. The largest value of last_active from the rows that satisfy the predicates will be returned.

You can see how the join operation works by eliminating the MAX() aggregate...

SELECT m.last_active
     , m.id           AS member_id
     , m.name         AS member_name
     , r.member
     , r.team
     , r.id AS roster_id
  FROM members m
  JOIN rosters r
    ON r.member = m.id
 WHERE r.team = 1
 ORDER BY m.last_active DESC

Upvotes: 1

Related Questions