Merhaba;
Profile tablosundaki account_id ile accounts tablosundaki account_id birbiri ile eşleştiğinden, inner join işlemine de bunu belirtmeniz gerekiyor.
where koşulunda @sessiondanGelecekUserId kısmına da uygulamanızda bulunan session'daki user_id'yi gönderebilirsiniz.
NOT: MSSQL'e göre yazılmıştır.


select a.name, p.alliance from accounts as a
inner join profile as p on p.account_id = a.account_id
where a.userid= @sessiondanGelecekUserId