mysql - 来自同一表的内部联接

我有四个桌子（目的地，度假村，酒店和多中心）。
在多中心表中，我有36列与其他三个表相关。
有没有一种方法可以使用内部联接将四个表连接在一起？

到目前为止，我有以下内容：

function getMultiCentres() {
    $db = new Db();
    $sql = "SELECT * FROM multicentres INNER JOIN destinations ON multicentres.destinationid_1 = destinations.destinationid AND multicentres.destinationid_2 = destinations.destinationid AND multicentres.destinationid_3 = destinations.destinationid AND multicentres.destinationid_4 = destinations.destinationid AND multicentres.destinationid_5 = destinations.destinationid AND multicentres.destinationid_6 = destinations.destinationid AND multicentres.destinationid_7 = destinations.destinationid AND multicentres.destinationid_8 = destinations.destinationid AND multicentres.destinationid_9 = destinations.destinationid AND multicentres.destinationid_10 = destinations.destinationid AND multicentres.destinationid_11 = destinations.destinationid AND multicentres.destinationid_12 = destinations.destinationid

    INNER JOIN resorts ON multicentres.resortid_1 = resorts.resortid AND multicentres.resortid_2 = resorts.resortid AND multicentres.resortid_3 = resorts.resortid AND multicentres.resortid_4 = resorts.resortid AND multicentres.resortid_5 = resorts.resortid AND multicentres.resortid_6 = resorts.resortid AND multicentres.resortid_7 = resorts.resortid AND multicentres.resortid_8 = resorts.resortid AND multicentres.resortid_9 = resorts.resortid AND multicentres.resortid_10 = resorts.resortid AND multicentres.resortid_11 = resorts.resortid AND multicentres.resortid_12 = resorts.resortid

    INNER JOIN hotels ON multicentres.hotelid_1 = hotels.hotelid AND multicentres.hotelid_2 = hotels.hotelid AND multicentres.hotelid_3 = hotels.hotelid AND multicentres.hotelid_4 = hotels.hotelid AND multicentres.hotelid_5 = hotels.hotelid AND multicentres.hotelid_6 = hotels.hotelid AND multicentres.hotelid_7 = hotels.hotelid AND multicentres.hotelid_8 = hotels.hotelid AND multicentres.hotelid_9 = hotels.hotelid AND multicentres.hotelid_10 = hotels.hotelid AND multicentres.hotelid_11 = hotels.hotelid AND multicentres.hotelid_12 = hotels.hotelid
     ";
    $multicentres = $db -> select($sql);
    return $multicentres;
}

最佳答案

我认为您不希望匹配所有列，而是每个表仅匹配一列。因此，使用AND代替IN运算符：

SELECT *
FROM multicentres m
INNER JOIN destinations d
ON d.destination IN (
  m.destination_1, m.destination_2, m.destination_3, m.destination_4, m.destination_5, m.destination_6,
  m.destination_7, m.destination_8, m.destination_9, m.destination_10, m.destination_11, m.destination_12
)
INNER JOIN resorts r
ON r.resortid IN (
  m.resortid_1, m.resortid_2, m.resortid_3, m.resortid_4, m.resortid_5, m.resortid_6,
  m.resortid_7, m.resortid_8, m.resortid_9, m.resortid_10, m.resortid_11, m.resortid_12
)
INNER JOIN hotels h
ON h.hotelid IN (
  m.hotelid_1, m.hotelid_2, m.hotelid_3, m.hotelid_4, m.hotelid_5, m.hotelid_6,
  m.hotelid_7, m.hotelid_8, m.hotelid_9, m.hotelid_10, m.hotelid_11, m.hotelid_12
)

也可以代替LEFT JOINS代替INNER JOIN。

关于mysql - 来自同一表的内部联接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58171366/