CODEWITHSUNDEEP
phpmysqljoininner-join
Luke
Luke

Reputation: 3

Mysql php: How to join same table multiple times?

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;
}

Upvotes: 0

Views: 644

Answers (1)

Devon Bessemer
Devon Bessemer

Reputation: 35357

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).

Upvotes: 2

Related Questions