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

score 1 · Accepted Answer · answered Oct 28 '18 at 13:26

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 = ?

OMG !!! I need to buy new eyes. Thank you very much too your help. I have to stay focus when I code sunday morning ;°). — Renjus, Oct 28 '18 at 17:53

score 1 · Answer 2 · answered Oct 28 '18 at 15:51

Because you are joining on two identically named columns in your ON clause you could instead use the alternative JOIN syntax with USING and the matching column(s) for your JOIN conditition (documentation). This would also remove the need for table aliases to identify to which table the id_vg in SELECT comes from.

So,

SELECT id_vg, ou_name, ou_info
FROM vignette_ou
INNER JOIN ref_vg_ou USING (id_vg)
WHERE ref = ?

will also work here.

Join ambiguous same value into select and on

2 Answers2