SQL查询报错Error Code:1052 - 字段'idk'歧义问题求助

Hey there! Let's get that 1052 - Column 'idk' in field list is ambiguous error sorted out. The root cause is simple: the idk column exists in both your nilai and matakuliah tables, and you're referencing it without specifying which table it belongs to in some parts of your query. MySQL can't guess which one you mean, hence the error.

Where the Problem Occurs #

Looking at your dynamic SQL generation code:

SET @sql_dinamis = (
    SELECT GROUP_CONCAT( DISTINCT
        CONCAT( 'max( IF(idk = ', idk, ',huruf,null) ) AS A', idk )
    ) FROM nilai
);

All three instances of idk here are missing a table prefix. Even though you're querying from the nilai table in this subquery, once this dynamic SQL is injected into your main query (which joins multiple tables), MySQL loses that context and sees idk as ambiguous.

The Fix #

You just need to explicitly tie every idk reference to the nilai table (since that's the source of the course IDs you're using to generate your pivot columns). Here's the corrected code:

Step 1: Fix the Dynamic SQL Generation

SET @sql_dinamis = (
    SELECT GROUP_CONCAT( DISTINCT
        CONCAT( 'max( IF(nilai.idk = ', nilai.idk, ',huruf,null) ) AS A', nilai.idk )
    ) FROM nilai
);

Step 2: Full Corrected Query

SET @sql_dinamis = (
    SELECT GROUP_CONCAT( DISTINCT
        CONCAT( 'max( IF(nilai.idk = ', nilai.idk, ',huruf,null) ) AS A', nilai.idk )
    ) FROM nilai
);

SET @SQL = CONCAT(
    'SELECT mahasiswa.nim, mahasiswa.nama, ', @sql_dinamis, '
     FROM nilai
     JOIN mahasiswa ON nilai.idm = mahasiswa.idm
     JOIN matakuliah ON nilai.idk = matakuliah.idk
     WHERE matakuliah.semester = 1
     GROUP BY nilai.idm'
);

-- Execute the dynamic query (don't forget this part!)
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Pro Tip for Future Queries #

To avoid this kind of error altogether, get in the habit of adding table prefixes (or short aliases) to every column in multi-table queries. For example, you could rewrite the main query with aliases to make it cleaner:

SET @SQL = CONCAT(
    'SELECT m.nim, m.nama, ', @sql_dinamis, '
     FROM nilai n
     JOIN mahasiswa m ON n.idm = m.idm
     JOIN matakuliah mk ON n.idk = mk.idk
     WHERE mk.semester = 1
     GROUP BY n.idm'
);

This makes your code more readable and eliminates ambiguity right from the start.

内容的提问来源于stack exchange，提问作者IrhasNh