MySQL JOIN get the value of a column based on another column in the same table-CodePudding

Table tagente


|id_agente | nombre | id_grupo |
|------------------------------|
| 3        |  mark  |    4     |

Table tagent_secondary_group

|id     | id_agente | id_group |
|------------------------------|
|  2    |    3      |    8     |

Table tgrupo

|id_grupo |   nombre   | parent|
|------------------------------|
|  8      | servers    |  10   |
|  10     | datacenter |  0    |

The "parent" column in the "tgrupo" table reference the "id_grupo" of the same table, So the parent of servers is datacenter.

And I need to get the "nombre" value based on the parent value.

How can I do this? It can be done on the same query?

SELECT  ta.nombre as agente ,tg.nombre as grupo , tg.parent 
FROM tagente ta
JOIN tagent_secondary_group tsg ON ta.id_agente  = tsg.id_agent 
JOIN tgrupo tg ON tsg.id_group = tg.id_grupo

CodePudding user response：

Yes, you just include the table twice with two different aliases. I write it here with WHERE, but it can be redone with JOINs.

SELECT  ta.nombre as agente , tg2.nombre as grupo , tg1.parent 
FROM tagente ta, tagent_secondary_group tsg, tgrupo tg1, tgrupo tg2
WHERE ta.id_agente = tsg.id_agent
  AND tsg.id_group = tg1.id_grupo
  AND tg1.parent = tg2.id_grupo;