是否可以有一个查询语句 - Is 1 single query statement possible

Is 1 single query statement possible

本文关键字：查询语句有一个是否 | 更新日期: 2023-09-26

对于本例，我有两个表—一对夫妇表和一个人表:

Persons Table
ID PERSON
1 Bob
2 Frank
3 Sally
4 Jane
Couples Table
ID HUSBAND WIFE
1  2  3
2  1  4

我是否可以编写一个查询语句来从两个表中进行选择，并以这样的方式将查询结果连接起来:

Couple 1 = Frank and Sally
Couple 2 = Bob and Jane

谢谢

SELECT Couples.ID, Husband.PERSON, Wife.PERSON
  FROM Couples
    INNER JOIN Persons AS Husband ON Couples.HUSBAND=Husband.ID
    INNER JOIN Persons AS Wife ON Couples.WIFE=Wife.ID

只是一个提示，但不是每一个婚姻都是丈夫/妻子。配偶1 &配偶二可能更适合未来。

像这样....

select m.id as husband_id, m.person as husband_name, f.id as wife_id, f.person as wife_name
from couples c
inner join persons m on m.id=c.husband
inner join persons f on f.id=c.wife

SELECT 'Couple ' + c.id + ' ' + h.person + ' and ' + w.person
FROM Couples c
JOIN Persons h ON h.id = c.husband
JOIN Persons w ON w.id = c.wife