mysql - 内连接错误: #1054 - Unknown column 'users.address_id' in 'on clause'

Question

正如错误所说:我认为我的查询出了问题..这是我在 MySQL 中的表设置:

用户:id - 用户名 - 密码 - salt 姓氏 - 姓名 - 加入 - email_id - requests_id - address_id - address_2_id

电子邮件:id - 电子邮件 - 订阅者

地址:id - 街道 - 街道号码 - 邮政编码 - 城市 - 国家/地区 - 电话 - btw_number - 公司

这是我想使用的查询:

SELECT 
    users.id,
    users.username,
    users.password,
    users.salt,
    users.surname,
    users.name,
    users.joined,
    users.email_id,
    users.permissions_id,
    users.address_id,
    users.address_2_id,

    emails.id,
    emails.email,
    emails.subscriber,

    a1.id AS a1_id,
    a1.street AS a1_street,
    a1.street_number AS a1_street_number,
    a1.zip_code AS a1_zip_code,
    a1.city AS a1_city,
    a1.country AS a1_country,
    a1.phone AS a1_phone,
    a1.btw_number AS a1_btw_number,
    a1.company AS a1_company,

    a2.id AS a2_id,
    a2.street AS a2_street,
    a2.street_number AS a2_street_number,
    a2.zip_code AS a2_zip_code,
    a2.city AS a2_city,
    a2.country AS a2_country,
    a2.phone AS a2_phone,
    a2.btw_number AS a2_btw_number,
    a2.company AS a2_company

FROM users, emails
INNER JOIN addresses AS a1 ON a1.id = users.address_id
INNER JOIN addresses AS a2 ON a2.id = users.address_2_id
WHERE users.username = 'araguera' AND users.email_id = emails.id

有人发现我的错误吗？

Answer 1

除了没有 address_id 列的表之外，我只能考虑您正在使用的隐式和显式连接的混合:

SELECT 
    u.id,
    u.username,
    u.password,
    u.salt,
    u.surname,
    u.name,
    u.joined,
    u.email_id,
    u.permissions_id,
    u.address_id,
    u.address_2_id,

    e.id,
    e.email,
    e.subscriber,

    a1.id AS a1_id,
    a1.street AS a1_street,
    a1.street_number AS a1_street_number,
    a1.zip_code AS a1_zip_code,
    a1.city AS a1_city,
    a1.country AS a1_country,
    a1.phone AS a1_phone,
    a1.btw_number AS a1_btw_number,
    a1.company AS a1_company,

    a2.id AS a2_id,
    a2.street AS a2_street,
    a2.street_number AS a2_street_number,
    a2.zip_code AS a2_zip_code,
    a2.city AS a2_city,
    a2.country AS a2_country,
    a2.phone AS a2_phone,
    a2.btw_number AS a2_btw_number,
    a2.company AS a2_company

FROM users AS u
INNER JOIN emails AS e
    ON u.email_id = e.id
INNER JOIN addresses AS a1 
    ON a1.id = users.address_id
INNER JOIN addresses AS a2 
    ON a2.id = users.address_2_id
WHERE u.username = 'araguera'