SQLTeam.com | Weblogs | Forums

Change id into name

so i have 2 merged databases and i want to output name, not id.
these are the results of the tournament, there are 4 places and instead of 1st_place = 25 which is id i want to output the name, not id. so instead of outputing 25 i want to output GreatGang

Databases:
Tournament t
and
Tournament_teams tt

Tournament t

t.id | t.first | t.second | t.third | t.fourth
12 | 22 | 33 | 44 | 55

Tournament_teams tt

tt.id | tt.name | tt.number | tt.id_tour
1 | felixTeam | 22 | 12
2 | funTeam | 33 | 12
3 | papaTeam | 44 | 12
4 | eppaTeam | 55 | 12

So i want the output to be

12 | felixTeam | funTeam | papaTeam | eppaTeam

It's just example btw :smiley:

Your Tournament table fails first normal form!

You can join to Tournament_teams multiple times using a different alias each time.

SELECT
t.nazwa name,t.`data` date,t.rodzaj type, t.min_lvl, t.max_lvl, t.max_lvl_suma, COUNT(distinct th.name) count_players, 
t.miejsce1 1st_place, t.miejsce2 2nd_place, t.miejsce3 3rd_place, t.miejsce3b 4th_place
FROM
turniej t
left JOIN turniej_druzyna td
ON t.id = td.id_turniej
LEFT JOIN turniej_hero th
ON t.id = th.id_tournament
WHERE t.`data` > '2022-04-01'
GROUP BY t.id
ORDER BY t.`data`

how to do this?
i've made it like this for now so i have the result shown on printscreen

Ok i solved it :stuck_out_tongue:

SELECT
	t.id,t.`data` date,t.rodzaj type, t.min_lvl, t.max_lvl, t.max_lvl_suma, 
	COUNT(distinct th.name) players, COUNT(DISTINCT td.nazwa) teams,
	td1.nazwa 1st_place, td2.nazwa 2nd_place, td3.nazwa 3rd_place, td4.nazwa 4th_place
FROM
	turniej t
		left JOIN turniej_druzyna td
		ON t.id = td.id_turniej
		
		left JOIN turniej_druzyna td1
		ON t.miejsce1 = td1.id
		
		left JOIN turniej_druzyna td2
		ON t.miejsce2 = td2.id
		
		left JOIN turniej_druzyna td3
		ON t.miejsce3 = td3.id
		
		left JOIN turniej_druzyna td4
		ON t.miejsce3b = td4.id
		
		LEFT JOIN turniej_hero th
		ON t.id = th.id_tournament
WHERE 
	t.`data` > '2022-04-01'
GROUP BY 
	t.id
ORDER BY 
	t.`data`

yes, thanks :slight_smile: