mysql - 在同一个 JOIN 中选择同一个表中的多行

Question

图中是我现在的 table 情况:

现在本例中的中心表是 tblJob，这里定义了我需要的所有内容(并非全部在图片中)。

地址表需要返回 2 个值(1 个公司值和 1 个工作本身值)。我现在唯一需要做的就是添加公司地址(工作地址已经在我的查询中)我的查询已经如下所示:

SELECT
tblJob.jobID,
tblJob.amount        AS jobAmount,
tblJob.extraInfo     AS jobExtraInfo,
tblJob.views         AS jobViews,
tblJob.description   AS jobDescription,
tblJob.dateCreated   AS jobDateCreated,
tblJobFunction.jobFunctionID,
tblJobFunction.jobFunction,
tblAddress.zipcode   AS jobAddress,
tblAddress.city      AS jobCity,
tblAddress.street    AS jobStreet,
tblAddress.number    AS jobNumber,
tblAddress.bus       AS jobBus,
tblCountry.countryID AS jobCountryID,
tblCountry.country   AS jobCountry,
tblCountry.areaCode  AS jobAreaCode,
tblCompany.companyID,
tblCompany.name,
tblCompany.email,
tblCompany.GSM,
tblCompany.phoneNumber,
tblCompany.photoURL  AS companyPhotoURL,
tblCompany.VATNumber,
tblCompany.websiteURL,
tblEvent.eventID,
tblEvent.event,
tblEvent.description AS eventDescription,
tblEvent.startDate   AS eventStartDate,
tblEvent.endDate     AS eventEndDate,
tblEvent.facebookURL,
tblEvent.photoURL    AS eventPhotoURL,
tblEvent.views       AS eventViews,
tblEvent.dateCreated AS eventDateCreated
FROM tblJob
JOIN tblAddress ON tblAddress.addressID = tblJob.addressID
JOIN tblCountry ON tblAddress.countryID = tblCountry.countryID
JOIN tblJobFunction ON tblJob.jobFunctionID = 
                       tblJobFunction.jobFunctionID
JOIN tblCompany ON tblJob.companyID = tblCompany.companyID
LEFT JOIN tblEvent ON tblJob.eventID = tblEvent.eventID

现在的问题是:如何在同一查询中添加公司的地址？

Answer 1

根据需要多次使用地址表，但每次都必须为其指定一个新别名:

FROM tblJob
JOIN tblAddress ON tblAddress.addressID = tblJob.addressID
JOIN tblCountry ON tblAddress.countryID = tblCountry.countryID
JOIN tblJobFunction ON tblJob.jobFunctionID =  tblJobFunction.jobFunctionID
JOIN tblCompany ON tblJob.companyID = tblCompany.companyID

JOIN tblAddress a2 ON a2.addressID = tblCompany.addressID

LEFT JOIN tblEvent ON tblJob.eventID = tblEvent.eventID

也许更像这样:

SELECT JobAddress.street, CompanyAddress.streetFROM tblJobJOIN tblAddress JobAddress ON JobAddress.addressID = tblJob.addressIDJOIN tblCompany ON tblJob.companyID = tblCompany.companyIDJOIN tblAddress CompanyAddress ON CompanyAddress.addressID = tblCompany.addressID