CODEWITHSUNDEEP
mysqlsql
Harry Potter
Harry Potter

Reputation: 13

Joining two tables together with multiple results in one table

My tables looks like the following:

tbl_teams
id, teamname, created

tbl_teamstats
id, rank, rating, wins, losses, tbl_teams_id, created

I want to merge these two tables together based on tbl_teamstats "created desc limit 1" for example the query

SELECT TS.*, T.* FROM tbl_teamstats as TS
LEFT JOIN tbl_teams as T
ON T.id = TS.tbl_teams_id

would return something like this

TS.id   TS.rank TS.rating   TS.wins TS.losses   T.id    T.teamname              T.created
14871   2       2522        168     26          2       teamname1               23/02/2017 17:55
14688   2       2540        168     25          2       teamname1               23/02/2017 17:55
2683    2       2535        167     25          2       teamname1               23/02/2017 17:55
2612    2       2529        166     25          2       teamname1               23/02/2017 17:55
2590    2       2523        165     25          2       teamname1               23/02/2017 17:55
2448    2       2517        164     25          2       teamname1               23/02/2017 17:55
2346    2       2511        163     25          2       teamname1               23/02/2017 17:55
234     2       2505        162     25          2       teamname1               23/02/2017 17:55
1       1       2570        171     19          1       teamname2               23/02/2017 17:55

what i want it to look like:

TS.id   TS.rank TS.rating   TS.wins TS.losses   T.id    T.teamname              T.created
14871   2       2522        168     26          2       teamname1               23/02/2017 17:55
1       1       2570        171     19          1       teamname1               23/02/2017 17:55

(Only showing one result from tbl_teamstat ordered by TS.id)

Could someone please help me or point me in the right direction? Would be greatly appreciated!

Upvotes: 1

Views: 52

Answers (2)

Putut Redianto
Putut Redianto

Reputation: 1

Add a GROUP BY clause at the end

SELECT TS.*, T.* FROM tbl_teamstats as TS
LEFT JOIN tbl_teams as T
ON T.id = TS.tbl_teams_id
GROUP BY TS.rank

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

You can find max id for each tbl_team_id from tbl_teamstats in a subquery and join it with tbl_teamstats. Then, do the joins as you need.

select *
from (
    select t1.*
    from tbl_teamstats t1
    join (
        select tbl_teams_id,
            max(id) id
        from tbl_teamstats
        group by tbl_teams_id
        ) t2 on t1.tbl_teams_id = t2.tbl_teams_id
        and t1.id = t2.id
    ) ts
left join tbl_teams as T on T.id = TS.tbl_teams_id

Upvotes: 1

Related Questions