我正在尝试在我的sql查询中做几个JOINS,但遇到了问题。我需要做的是从poker_sites
表中选择所有字段,然后从networks
中获取 2 个相对字段,如果可用,则从editor_content
获取平均评分
我遇到的问题是查询只返回一行,而至少应该有三行。
任何帮助将不胜感激。
这是我的 SQL
SELECT AVG(editor_content.rating) AS rating, poker_sites.*,
networks.network_name, networks.network_icon FROM poker_sites
LEFT JOIN networks
ON (poker_sites.network_id=networks.network_id)
LEFT JOIN editor_content
ON (poker_sites.site_id=editor_content.assign_id)
WHERE poker_sites.published=1
如果你想用聚合函数(在本例中为 AVG)获得多个结果,则需要 GROUP BY。
SELECT x.avgRating AS rating, poker_sites.*,
networks.network_name, networks.network_icon
FROM poker_sites
LEFT JOIN networks
ON (poker_sites.network_id=networks.network_id)
LEFT JOIN
(
SELECT AVG(editor_content.rating) as avgRating, editor_content.assign_id
FROM editor_content
GROUP BY editor_content.assign_id
) x
ON (poker_sites.site_id = x.assign_id)
WHERE poker_sites.published=1