Team table has:
ID | TEAM
--------+----------
1 | A
2 | B
Result table has:
fk_ID1 | fk_ID2 | RESULT
----------+-----------+-----------
1 | 2 | 5:0
2 | 1 | 2:3
How to Inner JOIN table, to get: (A 5:0 B) & (A 2:3 B)?
My code example:
public function getResultList($limit, $offset) {
$query = " SELECT result_id,
t1.name name1,
t2.name name2,
team1_goals,
team2_goals,
date
FROM results
INNER JOIN team t1 ON fk_tm1_id=tm_id
INNER JOIN team t2 ON fk_tm2_id=tm_id";
$data = mysql::select($query);
return $data;
}
It's best to answer this as purely an SQL question, which it is. You need to assign a table alias when joining the same table two or more times.
You seem to only be assigning aliases to the column. To assign an alias to a column or table, you can add the alias directly after the column or table name (AS can also be used but isn't necessary for MySQL)
A common thing is to number the tables as t1, t2, t3, etc.
SELECT t1.name name1, t2.name name2 FROM ...
INNER JOIN team_table t1 ON ...
INNER JOIN team_table t2 ON ...
This aliases the first join as t1 and the second join as t2, which you would use when accessing data from that specific join (SELECT t1.name).