mysql inner join ambigious USING() field but not always
I've recently changed servers and although I'm still apparently using MySQL, I don't think it's the same version or set up in the same way that I'm used to.
The most pronounced effect is on my JOIN queries, essentially some queries that use INNER JOINs are throwing ambigious column errors where on my last server I'm sure the code would be fine.
table1
t1_id int primary key
name text
map
t1_id int
t2_id int
table2
t2_id int primary key
name text
table1 has a one to many relationship with table2. Normally, to get a row from table1 and all its table2 entries, I'd do something like this:
SELECT * FROM table1
INNER JOIN map USING(t1_id)
INNER JOIN table2 USING(t2_id)
WHERE table1.t1_id=1;
However this throws an ambigious column error every time. The only way round this is to use
SELECT * FROM table1
INNER JOIN map ON(table1.t1_id=map.t1_id)
INNER JOIN table2 ON(map.t2_id=table2.t2_id)
WHERE table1.t1_id=1;
There isn't much I can tell you about the server config aside from the version, 5.1.56-community-log, and I beleive it's running Linux (probably Debian).
Is this a problem with my queries or a somethi开发者_开发知识库ng to do with the version of MySQL we are using?
Have you tried the following:
SELECT table1.t1_id, table1.name FROM table1
INNER JOIN map USING(t1_id)
INNER JOIN table2 USING(t2_id)
WHERE table1.t1_id=1;
You can get any coulmn you want from any table, just alias (temp. rename) those that have same names:
SELECT table1.t1_id
, table1.name AS t1_name
, table2.t2_id
, table2.name AS t2_name
FROM table1
INNER JOIN map USING(t1_id)
INNER JOIN table2 USING(t2_id)
WHERE table1.t1_id=1;
精彩评论