CODEWITHSUNDEEP
mysqlsqljoingreatest-n-per-group
Josh
Josh

Reputation: 118

MySQL - combining multiple queries

I currently have the following queries (and some surrounding vB code). I was hoping I could slim this up into a single SELECT statement versus having to run 10 more on each page to get that names of individuals. 

$results = $db->query_read_slave("
    SELECT user.id AS steam, user.skills AS level
    FROM wcsp.wcgousers AS user
    WHERE race = '_wcs_' 
    ORDER BY ABS(user.skills) DESC LIMIT 10
    ");

$rank = 1;
while ($user = $db->fetch_array($results)) {
    $result = $db->query_first("
        SELECT old.name AS name
        FROM wcsp.warn_oldnames AS old
        WHERE id = '$user[steam]'
        ORDER BY lasttime
        DESC LIMIT 1
    ");

    $listing[] = array("id" => $user['steam'], "level" => $user['level'], "name" => $result['name'], "rank" => $rank);

    $rank += 1;
}

I have tried LEFT JOIN but the issue I run into is that I would need a subquery in the LEFT JOIN similar to:

SELECT user.id AS steam, user.skills AS level, names.name AS name
FROM wcsp.wcgousers AS users
LEFT JOIN 
    (
        SELECT names.name
        FROM wcsp.warn_oldnames AS names
        WHERE id = wcsp.wcgousers.id
        ORDER BY lasttime DESC LIMIT 1
    ) AS names
ON names.id = users.id
WHERE users.race = '_wcs_'

Which won't work due to the different database check inside the subquery.

Upvotes: 2

Views: 344

Answers (1)

John Woo
John Woo

Reputation: 263893

If I understand you correctly, you want to get the latest Name for every users. 

SELECT  a.id AS steam, 
        a.skills AS level,
        b.name
FROM    wcgousers a
        INNER JOIN warn_oldnames b
            ON a.ID = b.ID
        INNER JOIN
        (
            SELECT  ID, MAX(lasttime) max_date
            FROM    warn_oldnames
            GROUP   BY ID
        ) c ON  b.ID = c.ID AND
                b.lastTime = c.max_date
WHERE   a.race = '_wcs_'
-- ORDER  BY ABS(a.skills) DESC 
-- LIMIT  10

Upvotes: 1

Related Questions