Duplicate column on join

问题:

I'm trying to join three tables, after filtering one down to the most recent entry per user. However, all of a sudden I'm running into the error Duplicate column name 'username'. I need to join on this "duplicate" column. How do I fix this?

SELECT customers.id,customers.name,customers.username,customers.phone,customers.email,radcheck.value as password
    FROM customers
    RIGHT JOIN radcheck ON customers.username = radcheck.username
    LEFT JOIN (
            SELECT * FROM radacct INNER JOIN (
                SELECT username,MAX(acctupdatetime) AS latest FROM radacct GROUP BY username
            ) as radrecent 
            ON radacct.username = radrecent.username 
            AND radacct.acctupdatetime = radrecent.latest
    ) as radlatest 
        ON customers.username = radlatest.username
    WHERE radcheck.attribute = 'Cleartext-Password'

回答1:

In the * you have two columns that are username. You need to qualify one or both of them. Example below:

SELECT 
   customers.id,customers.name,customers.username,customers.phone,customers.email,radcheck.value as password
        FROM customers
        RIGHT JOIN radcheck ON customers.username = radcheck.username
        LEFT JOIN (
                SELECT radrecent.username, latest FROM radacct INNER JOIN (
                     --^^^^^^^^^
                    SELECT username,MAX(acctupdatetime) AS latest FROM radacct GROUP BY username
                ) as radrecent 
                ON radacct.username = radrecent.username 
                AND radacct.acctupdatetime = radrecent.latest
        ) as radlatest 
            ON customers.username = radlatest.username
        WHERE radcheck.attribute = 'Cleartext-Password'