mysql - MySql - 双左连接有问题
问题描述
我在获取此双左连接以获取 listingspecificsListPrice 时遇到问题,但该信息存在于表中,无法弄清楚为什么它不包含它。这是我的sql。
SELECT mls_subject_property.*, mls_images.imagePath, mls_forms_listing_specifics.listingspecificsListPrice
FROM mls_subject_property
LEFT JOIN mls_images ON mls_subject_property.mls_listingID = mls_images.mls_listingID
LEFT JOIN mls_forms_listing_specifics ON mls_forms_listing_specifics.mls_listingID = mls_subject_property.mls_listingID AND mls_images.imgOrder = 0
WHERE userID = 413
GROUP BY mls_subject_property.mls_listingID
解决方案
mls_images.imgOrder = 0
条件应该在 join with 中,mls_images
而不是mls_forms_listing_specifics
。
GROUP BY
如果您不使用任何聚合函数,请不要使用。用于SELECT DISTINCT
防止重复。
SELECT DISTINCT mls_subject_property.*, mls_images.imagePath, mls_forms_listing_specifics.listingspecificsListPrice
FROM mls_subject_property
LEFT JOIN mls_images ON mls_subject_property.mls_listingID = mls_images.mls_listingID AND mls_images.imgOrder = 0
LEFT JOIN mls_forms_listing_specifics ON mls_forms_listing_specifics.mls_listingID = mls_subject_property.mls_listingID
WHERE userID = 413
推荐阅读
- javascript - 如何在没有人检测到的情况下将全局函数包装在 javascript 中?
- network-programming - Here's a weird one
- laravel - Laravel whereBetween(query) Does not return the correct data. How can query based on latest report of each project?
- encoding - How can I interpret coefficients after target encoding with multi-class?
- python-3.x - Boxplot for timeseries: The truth value of a Series is ambiguous error
- reactjs - Calculate width after component has rendered?
- r - 如何删除 R 中与前一行重复的行?
- java - Trying to make a program that adds all the digits in a number together and im struggling
- r - 从R中的tidygraph对象列表返回列表索引?
- amazon-web-services - Is there any easy way to recreate existing Amplify application as a different Amplify Application?