选择4个参与活动的用户个人资料图片(mysql)

Question

我正在使用以下查询从我的数据库中获取有关meetup (一个事件)的信息。我想能够选择4个用户(谁参与活动)个人资料图片。

SELECT
    m.meetup_name,
    m.url_meetup,
    LEFT(m.meetup_text, 120) as teaser,
    DATE_FORMAT(m.datetime, "%d-%m-%Y <br> %I:%i") as datetime,
    a.username,
    a.url_username,
    c.city_name,
    (SELECT count(*) FROM meetup_participants mp WHERE MP.meetup_id = m.id) as participants
FROM
meetups m, users a, cities c
WHERE
    a.id = m.author_id
    AND c.postalcode = m.postalcode

我的表格如下所示：

meetup =>
  id
  meetup_name
  url_meetup
  meetup_text
  author_id
  datetime
  ......

meetup_participants =>
  meetup_id
  user_id

users
  id
  username
  url_username
  profile_picture

我强烈希望不再使用任何子查询。我的第一个想法是可以通过已有的子查询执行此操作，但随后我发现只能从子查询中返回一列。

我希望有人知道如何才能做到这一点。

Answer 1

我最后在子查询中使用了CONCAT，并使用php将字符串拆分成一个数组：

(SELECT GROUP_CONCAT(CONCAT(u.url_username,"/60_thumb/",u.profile_picture)) FROM meetup_participants mp, users u WHERE mp.meetup_id = m.id AND u.id = mp.user_id LIMIT 4) AS participants_pictures

Answer 2

如果我正确理解了您的表，这应该是为会议找到的前四个参与者。通过meetup_participants解析表连接用户。

SELECT
  m.meetup_name,
  m.url_meetup,
  LEFT(m.meetup_text, 120) as teaser,
  DATE_FORMAT(m.datetime, "%d-%m-%Y <br> %I:%i") as datetime,
  a.username,
  a.url_username,
  c.city_name,
  mp.picture -- or whatever you need to get from meeting_participants
FROM meetups
INNER JOIN meetup_participants mp ON m.id = m.meetup_id
INNER JOIN users a ON mp.user_id = a.id
INNER JOIN cities c ON m.postalcode = c.postalcode
WHERE m.whatever = 'whatever identifies the event'
ORDER BY a.username -- or whatever is important to you, or don't ORDER the results
LIMIT 4