一个查询中的 MySQL 多个连接？

MySQL Multiple Joins in one query?

提问by Drew

I have the following query:

我有以下查询：

SELECT
  dashboard_data.headline,
  dashboard_data.message,
  dashboard_messages.image_id 
FROM dashboard_data
INNER JOIN dashboard_messages
  ON dashboard_message_id = dashboard_messages.id

So I am using an INNER JOINand grabbing the image_id. So now, I want to take that image_id and turn it into images.filenamefrom the images table.

所以我正在使用 anINNER JOIN并抓取image_id. 所以现在，我想获取那个 image_id 并将其images.filename从图像表中转换。

How can I add that in to my query?

如何将其添加到我的查询中？

回答by Code Magician

You can simply add another join like this:

您可以简单地添加另一个连接，如下所示：

SELECT dashboard_data.headline, dashboard_data.message, dashboard_messages.image_id, images.filename
FROM dashboard_data 
    INNER JOIN dashboard_messages 
        ON dashboard_message_id = dashboard_messages.id
    INNER JOIN images
        ON dashboard_messages.image_id = images.image_id 

However be aware that, because it is an INNER JOIN, if you have a message without an image, the entire row will be skipped. If this is a possibility, you may want to do a LEFT OUTER JOINwhich will return all your dashboard messages and an image_filename only if one exists (otherwise you'll get a null)

但是请注意，因为它是INNER JOIN，如果您有一条没有图像的消息，则整行将被跳过。如果这是可能的，您可能想要执行一个LEFT OUTER JOIN仅当存在时才返回所有仪表板消息和图像文件名（否则您将获得空值）

SELECT dashboard_data.headline, dashboard_data.message, dashboard_messages.image_id, images.filename
FROM dashboard_data 
    INNER JOIN dashboard_messages 
        ON dashboard_message_id = dashboard_messages.id
    LEFT OUTER JOIN images
        ON dashboard_messages.image_id = images.image_id 

回答by Brian Driscoll

Just add another join:

只需添加另一个连接：

SELECT dashboard_data.headline,
       dashboard_data.message,
       dashboard_messages.image_id,
       images.filename 
FROM dashboard_data 
    INNER JOIN dashboard_messages
            ON dashboard_message_id = dashboard_messages.id 
    INNER JOIN images
            ON dashboard_messages.image_id = images.image_id

回答by Varun Tej Reddy

I shared my experience of using two LEFT JOINS in a single SQL query.

我分享了在单个 SQL 查询中使用两个 LEFT JOINS 的经验。

I have 3 tables:

我有3张桌子：

Table 1) Patient consists columns PatientID, PatientName

表 1) Patient 由 PatientID、PatientName 列组成

Table 2) Appointment consists columns AppointmentID, AppointmentDateTime, PatientID, DoctorID

表 2) 预约由 AppointmentID、AppointmentDateTime、PatientID、DoctorID 列组成

Table 3) Doctor consists columns DoctorID, DoctorName

表 3) Doctor 由 DoctorID、DoctorName 列组成

---

---

Query:

询问：

SELECT Patient.patientname, AppointmentDateTime, Doctor.doctorname

FROM Appointment 

LEFT JOIN Doctor ON Appointment.doctorid = Doctor.doctorId  //have doctorId column common

LEFT JOIN Patient ON Appointment.PatientId = Patient.PatientId      //have patientid column common

WHERE Doctor.Doctorname LIKE 'varun%' // setting doctor name by using LIKE

AND Appointment.AppointmentDateTime BETWEEN '1/16/2001' AND '9/9/2014' //comparison b/w dates 

ORDER BY AppointmentDateTime ASC;  // getting data as ascending order

---

---

I wrote the solution to get date format like "mm/dd/yy"(under my name "VARUN TEJ REDDY")

我编写了获取日期格式的解决方案，如“mm/dd/yy”（以我的名字“VARUN TEJ REDDY”）

回答by Mohit

Multi joins in SQL work by progressively creating derived tables one after the other. See this link explaining the process:

SQL 中的多重连接通过一个接一个地逐步创建派生表来工作。请参阅此链接以解释该过程：

https://www.interfacett.com/blogs/multiple-joins-work-just-like-single-joins/

https://www.interfacett.com/blogs/multiple-joins-work-just-like-single-joins/