ERROR 1052 (23000) at line 1: Column 'id' in field list is ambiguous

Question

I am trying to get all blogs that are having their related sites been expired before a specific date blog is a table with foreign key for site (siteId) here my SQL code:

SELECT * FROM blog b 
JOIN site s ON s.id=b.siteId 
WHERE (s.expiryDate < '2021-03-01') 
AND (b.plagiarismStatus=9 OR b.plagiarismStatus=1) 
AND b.isDeleted=0 AND b.isClosed=1

I keep getting this error:

ERROR 1052 (23000) at line 1: Column 'id' in field list is ambiguous

any help will be too much appreciated

score 3 · Accepted Answer · answered Apr 15 '21 at 07:43

Most likely both the blog and site tables have an id column. You should explicitly list out all columns you want to select:

SELECT
    b.id AS b_id,
    s.id AS s_id,
    b.siteId,
    s.expiryDate
    -- plus anything else you want to select
FROM blog b
INNER JOIN site s
    ON s.id = b.siteId
WHERE
    s.expiryDate < '2021-03-01' AND
    b.plagiarismStatus IN (1, 9) AND
    b.isDeleted = 0 AND
    b.isClosed = 1;

When you used SELECT * you were selecting an id column from each of the two tables. MySQL is rolling over and telling you that it doesn't know how to make sense of this.

ERROR 1052 (23000) at line 1: Column 'id' in field list is ambiguous

1 Answers1