来自另一个表的列的SQL命令。

I have 3 tables: people, groups and memberships. Memberships is a join table between people and groups, and have 3 columns: personId, groupId and description (text).

我有3张桌子:人，团体和会员资格。Memberships是一个人和组之间的连接表，有3列:personId、groupId和description (text)。

I want to select entries from the memberships table depending on a groupId but sorting the result by the names of people associated to the found memberships (name is a column of people table)

我希望根据groupId从memberships表中选择条目，但是要根据与找到的memberships关联的人员的名称对结果进行排序(name是people表的列)

SELECT * FROM "memberships" WHERE ("memberships".groupId = 32) ORDER BY (?????)

Is it possible to achieve this in one single query?

是否可能在一个查询中实现这一点?

3 个解决方案

#1

Join to the people table and then order by the field that you want.

加入到人员表，然后按所需字段进行排序。

SELECT
  m.* 
FROM 
  "memberships" AS m
  JOIN "people" AS p on p.personid = m.personID
WHERE
  m.groupId = 32
ORDER BY 
  p.name

#2

SELECT *
FROM Membership AS m
     JOIN People as p ON p.personID = m.personID
WHERE m.groupID = 32
ORDER BY p.name

#3

SELECT
      M.* ,
      P.Name AS PersonName
FROM 
      Memberships AS m
INNER  JOIN 
      People AS P ON P.PersonID = M.PersonID
WHERE
      M.GroupID = 32
ORDER BY 
      PersonName