开发者

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;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜