mysql - 加入city_name的用户和事件

我正在尝试获取事件的城市名称和用户居住的城市名称。它们都具有邮政编码，该邮政编码对应于表cities中的city_name。

到目前为止我的查询：

SELECT
    meetups.meetup_name,
    meetups.meetup_text,
    users.username,
    cities.city_name
FROM
    meetups, users, cities
WHERE
    meetups.url_meetup = ? and users.id = meetups.author_id and cities.postalcode = meetups.postalcode

我仍然需要获取用户的城市名称。

我的表如下所示：

meetups =>
    id
    meetup_url
    meetup_name
    meetup_text
    author_id
    postalcode

users =>
    id
    username
    postalcode

cities =>
    postalcode
    city_name

最佳答案

SELECT
    meetups.meetup_name,
    meetups.url_meetup,
    meetups.meetup_text,
    users.username,
    c1.city_name as meetup_city,
    c2.city_name as user_city
FROM
meetups, users, cities c1, cities c2
WHERE
url_meetup = ?
and users.id = meetups.author_id
and c1.postalcode = meetups.postalcode
and c2.postalcode = users.postalcode

这也应该使您获得用户城市的名称。

关于mysql - 加入city_name的用户和事件，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/16071709/