A column parameter is ambiguous when I join two tables

Question

Both tables (zielpersonen and wegeinland) have a column named as WP. So when I join them, there is an error saying that the column reference "WP" is ambiguous. Actually I want to use WP coming from zielpersonen table for my calculations. How can I avoid this error?

SELECT SUM("WP"*"w_rdist")/(62868*AVG("WP"))  FROM mobility.mzmv2010.zielpersonen 
JOIN mobility.mzmv2010.wegeinland USING ("HHNR","ZIELPNR")

you need to provide explicitly by what field you are [joining tables](http://dev.mysql.com/doc/refman/5.7/en/join.html). This is why you getting this error. Reference the manual. — Farside, Apr 19 '16 at 10:17
Possible duplicate of [MySQL join with where clause](http://stackoverflow.com/questions/1219909/mysql-join-with-where-clause) — Farside, Apr 19 '16 at 10:19
So which WP column do you want to use? Either specify `mobility.mzmv2010.zielpersonen.wp` or `mobility.mzmv2010.wegeinland.wp`. — jarlh, Apr 19 '16 at 10:26

Radu Gheorghiu · Accepted Answer · 2016-04-19T10:19:50.353

In your query, use aliases for your tables and then prefix the columns you want with the corresponding table aliases.

SELECT
    SUM(zp."WP"*"w_rdist")/(62868*AVG(zp."WP"))  
FROM mobility.mzmv2010.zielpersonen zp
JOIN mobility.mzmv2010.wegeinland wg 
    USING ("HHNR","ZIELPNR")

This is generally a best practice for when you're accessing more tables in a query, so make sure you prefix all of your columns in a query.

By doing this you're actually being a bit proactive since if there is a chance a column which is now in a single table will be added to your second table, this query will then fail and will require correction (which I guess is something you wouldn't want to happen in a production environment).

score 0 · Answer 2 · answered Apr 19 '16 at 10:14

You should use table aliases and qualified column names. Not just in this query, but as a good habit:

SELECT SUM(p."WP" * "w_rdist")/ (62868 * AVG(p."WP"))
FROM mobility.mzmv2010.zielpersonen p JOIN
     mobility.mzmv2010.wegeinland w
     USING ("HHNR", "ZIELPNR")

I don't know where w_rdist comes from, but that should also have a table alias.

A column parameter is ambiguous when I join two tables

2 Answers2