So, am bad at words, i will describe it as in pictures.
This is MATSH table in my database. My goal, is to replace Mang_id, Voistkond_1_id and Voistkond_2_id with names from other tables.
MATSH.Mang_id = MANG.Mang_id
MATSH.Voistkond_1_id and Voistkond_2_id are same as in VOISTKONNAD.Voistkond_id
The all 3 tables have all primary and forgein keys.
MATSH
VOISTKONNAD
MANG
But now I am trying to select from table info as the numbers are replace, but then there is a problem. That my Teine_voistkond is taking info from Voistkond_1_id not from Voistkond_2_id...
select MATSH.Matsh_id, MANG.Mang_nimi, MATSH.Voistkond_1_id, VOISTKONNAD.Voistkond_nimi as Esimene_voistkond, MATSH. Voistkond_2_id, VOISTKONNAD.Voistkond_nimi as Teine_voistkond, MATSH.Matsh_kuupaev, MATSH.Tulemus, MATSH.Muu_info
from MATSH
join MANG on MATSH.Mang_id = MANG.Mang_id
join VOISTKONNAD on MATSH.Voistkond_1_id = VOISTKONNAD.Voistkond_id;
But I if i use
...
join VOISTKONNAD on MATSH.Voistkond_1_id = VOISTKONNAD.Voistkond_id
join VOISTKONNAD on MATSH.Voistkond_2_id = VOISTKONNAD.Voistkond_id;
This gives me
ERROR 1066 (42000): Not unique table/alias: 'VOISTKONNAD'
I tried with aliases. It gives me.
Empty set (0.00 sec)
And with right and left join. It gives me again
Empty set (0.00 sec)
As you probably can understand now, my problem lies in the fact that the Teine_voistkond is not showing the names it should show.
and i simpy find the mistake anymore.