Join ambiguous same value into select and on

Question

I got some problems with INNER JOIN.

Table ref_vg_ou :

id_vg | ref
  12     1
  13     1
  14     2

Table vignette_ou :

id_vg | ou_name | ou_info
  12     ezzaez   eaezaeae
  13      tbtrb    grtrr
  14      hyht      yhty

mySQL request :

SELECT id_vg, ou_name, ou_info
        FROM vignette_ou AS vg
        INNER JOIN ref_vg_ou AS ref
            ON vg.vg_id = ref.vg_id
        WHERE ref.ref = ?

My console return an error :

'id_vg' field list is ambiguous

I don't understand why my "id_vg" is ambiguous ? I tried with Alias, but I got a new error :

SELECT vg.id_vg, vg.ou_name, vg.ou_info
        FROM vignette_ou AS vg
        INNER JOIN ref_vg_ou AS ref
            ON vg.vg_id = ref.vg_id
        WHERE ref.ref = ?

Error :

'vg.vg_id' unknown on clause

Answer 1

• Your sample table structure shows that you don't have a vg_id column, but id_vg.
• In case of queries involving multiple table, it is preferable to use name of the Table, or defined Alias (if any), to refer respective column(s). You are getting "ambiguous" column error message, because you have id_vg column in both the tables. MySQL is not able to identify which one to pick (without proper table name or Alias reference given).
• Also, note that once you have defined the Alias on a table, you can only refer to its column(s) using that Alias.

Try the following instead:

SELECT vg.id_vg, vg.ou_name, vg.ou_info
FROM vignette_ou AS vg
INNER JOIN ref_vg_ou AS ref
  ON vg.id_vg = ref.id_vg
WHERE ref.ref = ?