PosgreSQL - column referece is ambiguous-CodePudding

I am trying to get a number of units per room. I have two separate tables rooms and units. Room can be only one, but can have multiple units. I am trying to get a list of rooms with number of units for each. This is even if there are 0 units in given room. I worked to the point I wanted to print in the table also the room_id. room_id figures both in room table and unit table. Therefore I am getting error message stating that room_id is ambiguous. Of course I would expect this to understand that I want room_id from the room table.

I have following query:

SELECT count(ucr.*) units_no
, ucr.room_name
, ucr.room_image
, ucr.room_id
FROM (
SELECT u.*
, r.room_image
, r.room_name
, r.room_id
FROM unit u
LEFT JOIN room r ON r.room_id = u.room_id
WHERE r.room_id = 'b6229c33-a37e-4457-8fb0-941d632c2540'
) ucr
GROUP BY ucr.room_name, ucr.room_image, ucr.room_id;

I am getting following error: column reference "room_id" is ambiguous

I have tried following: , ucr.r.room_id

Also following: , ucr(r.room_id)

Also following: , ucr.(r.room_id)

I run out of options. How do I do this? Thank you for taking your time having a look on this issue.

CodePudding user response：

The unit and room tables both have a column called room_id. Therefore, the inner select is ambiguous:

SELECT u.*, r.room_image, r.room_name, r.room_id

because it isn't clear which room_id value to use in the outer query. You could alias the two room_id columns to unique names, but given that your query doesn't even seem to need the columns from the unit table, I would suggest:

SELECT COUNT(ucr.room_name) units_no,
       ucr.room_name,
       ucr.room_image,
       ucr.room_id
FROM (
    SELECT r.room_image, r.room_name, r.room_id
    FROM unit u
    LEFT JOIN room r ON r.room_id = u.room_id
    WHERE r.room_id = 'b6229c33-a37e-4457-8fb0-941d632c2540'
) ucr
GROUP BY ucr.room_name, ucr.room_image, ucr.room_id;

Actually, the subquery itself seems unnecessary and we can just use:

SELECT r.room_image, r.room_name, r.room_id, COUNT(*) AS units_no
FROM unit u
LEFT JOIN room r
    ON r.room_id = u.room_id AND
       r.room_id = 'b6229c33-a37e-4457-8fb0-941d632c2540'
GROUP BY r.room_image, r.room_name, r.room_id;

CodePudding user response：

Just add or change r.room_id alias, because room_id is on unit table and room table. so one of them need get other name, so best solution change alias :

SELECT count(ucr.*) units_no
, ucr.room_name
, ucr.room_image
, ucr.rooom_id
FROM (
SELECT u.*
, r.room_image
, r.room_name
, r.room_id as rooom_id
FROM unit u
LEFT JOIN room r ON r.room_id = u.room_id
WHERE r.room_id = 'b6229c33-a37e-4457-8fb0-941d632c2540'
) ucr
GROUP BY ucr.room_name, ucr.room_image, ucr.rooom_id;