Database query failed: Column 'id' in field list is ambiguous

Question

       SELECT  realestate.id,node.id,node.name
                from realestate,
                (SELECT
                    GROUP_CONCAT(parent.name_en ORDER BY parent.lft DESC) AS name,id
                  FROM
                    address AS node JOIN address AS parent
                    ON node.lft BETWEEN parent.lft AND parent.rgt  
                  WHERE node.lft >={$lft} and node.rgt <={$rgt}
                  GROUP BY node.name) as node
        INNER JOIN users ON id_user = users.id
        LEFT JOIN pic ON id_realestate = realestate.id
        WHERE node.id = realestate.address_id

I tried to modify it several times and this error message would not disappear "Database query failed: Column 'id' in field list is ambiguous". is there any idea on how to solve this?

Remind me, why are we grabbing parent ids? – Strawberry May 20 '13 at 09:18 — Strawberry, May 20 '13 at 09:18

score 2 · Accepted Answer · answered May 20 '13 at 08:00

The problem is on the subquery,

SELECT  GROUP_CONCAT(parent.name_en ORDER BY parent.lft DESC) AS name,
        id  -- <<== HERE (change to node.ID or parent.ID)
FROM    address AS node 
        JOIN address AS parent
           ON node.lft BETWEEN parent.lft AND parent.rg

You should specify on what table does the ID come from since both node and parent contains ID which causes ambiguity. It could be node.ID or parent.ID

score 2 · Answer 2 · answered May 20 '13 at 08:00

2

this is because you are using id in your concatenated query, so it's ambiguous, simply change to avoid the error

GROUP_CONCAT(parent.name_en ORDER BY parent.lft DESC) AS name,node.id --here you need to specify that is from node

answered May 20 '13 at 08:00

Fabio

23,183
12
55
64

1

+1 btw the sql comment is `--` not `//` :) – Stephan May 20 '13 at 08:04

score 0 · Answer 3 · answered May 20 '13 at 07:59

Change this:

GROUP_CONCAT(parent.name_en ORDER BY parent.lft DESC) AS name,id

with

GROUP_CONCAT(parent.name_en ORDER BY parent.lft DESC) AS name,node.id

you need to specify the alias of the table because mysql doesn't know what table to use to get the id column (assuming that both tables have a column named id).

Database query failed: Column 'id' in field list is ambiguous

3 Answers3