There are 2 tables, a table with Contracts , connected 1 to many with the table Firms .

Contracts:

 +----+--------+---------+ | id | firm_1 | firm_2 | +----+--------+---------+ | 1 | 1 | 2 | | 1 | 3 | 2 | | 1 | 1 | 1 | +----+--------+---------+

Firms:

 +----+------+ | id | name | +----+------+ | 1 | ООО | | 2 | ИП | | 3 | ОАО | +----+------+

When displaying the Contracts table, it is necessary to output the company name id . Example output:

 +----+--------+---------+ | id | firm_1 | firm_2 | +----+--------+---------+ | 1 | ООО | ИП | | 1 | ОАО | ИП | | 1 | ООО | ООО | +----+--------+---------+

At the moment, the query looks like this and works only for 1 field:

 SELECT id , Firms.name, firm_2 FROM Contracts LEFT JOIN Firms ON Firms.id = Contracts.firm_1;

    2 answers 2

    Attach the Firms table again to use it for the second field. To distinguish the Firms tables (if they are joined several times), it is necessary to use aliases (pseudonyms) that will allow to distinguish the tables.

     SELECT c.id, f1.name, f2.name FROM Contracts с LEFT JOIN Firms f1 ON f1.id = Contracts.firm_1 LEFT JOIN Firms f2 ON f2.id = Contracts.firm_2

      It is necessary to table the second time. There is one not the most obvious thing - in this case it is necessary to use aliases for tables.

       SELECT Contracts.id , f1.name, f2.name FROM Contracts LEFT JOIN Firms as f1 ON f1.id = Contracts.firm_1 LEFT JOIN Firms as f2 ON f2.id = Contracts.firm_2;

      If for each Contracts must be an entry in Firms , then hang up the foreign key to verify this fact and use the INNER JOIN instead of the LEFT JOIN .

      • What's the difference for this example in INNER JOIN and LEFT JOIN? - users
      • @ Skar404 for this example in nothing - the result will be the same. - Denis
      • @ Small here you need an alias for id in the SELECT line, because it is also in the Firms table - Denis
      • @Denis, yes indeed. Thanks, corrected. Skar404, if for all Contracts there will be an entry in Firms - then the question is that it will not differ in any way. And why then left to do? - Small
      • @ Small in general for all records there is an entry in Firms , but in any cases, sampling of all entries that are in Contracts and if there is anything to output Null , but simply not to output a string, or I don’t understand the difference INNER JOIN and LEFT JOIN - users