PHP / MySQL - Join / Union两个表的结果

this is my first post on Stack Overflow, so I'll try to provide as much information as possible. I've spent hours trying to figure this out, and thought that maybe some help would get me over the hump with this project.

这是我在Stack Overflow上的第一篇文章,所以我会尽量提供尽可能多的信息。我花了好几个小时试图解决这个问题,并认为可能有一些帮助可以帮助我解决这个问题。

I'm trying to create a review site using Mysql 5/Php5 and I have two tables:

我正在尝试使用Mysql 5 / Php5创建一个评论网站,我有两个表:

'companies'
'reviews'

In the companies table, I have a full list of all US companies, and in the reviews table, I have a list of reviews based on companies from the "companies" table. The reviews table is populated when users submits a review via a form.

在公司表中,我列出了所有美国公司的完整列表,在评论表中,我有一个基于“公司”表中公司的评论列表。当用户通过表单提交评论时,将填充评论表。

Thus, I want to have a search results page, that will list all companies by alphabet. In the table for the search results, the company name would be listed, the total reviews, and the total score.

因此,我希望有一个搜索结果页面,它将按字母顺序列出所有公司。在搜索结果的表格中,将列出公司名称,总评论和总分。

In the companies table, I have the following columns:
company_id
company
address
city
state

在公司表中,我有以下列:company_id公司地址城市州

In the reviews table, I have the following columns:
'review_id'
'review_date'
'company'
'review'
'review_city'
'review_state'
'comp_benefits'
'work_life_balance'
'job_security'
'career_growth'

在评论表中,我有以下列:'review_id''review_date''company''review''review_city''review_state''comp_benefits''work_life_balance''work_security''customer_growth'

Currently, I wrote a SQL script that outputs all companies from the 'review' table, a sum of it's score and a total review count:

目前,我编写了一个SQL脚本,输出“评论”表中的所有公司,它的总分和总评论数:

$result = mysql_query('select *, SUM(comp_benefits + work_life_balance + job_security + career_growth) as ItemSum, COUNT(company) as TotalReviews from reviews where company LIKE "'.$letter.'%" GROUP BY company;');

$ result = mysql_query('select *,SUM(comp_benefits + work_life_balance + job_security + career_growth)为ItemSum,COUNT(公司)为TotalReviews,来自评论公司LIKE“'。$ letter。'%'GROUP BY company;');

So, output would be as such:

所以,输出将是这样的:

"Best Buy" "35" "3"

“百思买”“35”“3”

The problem is, this query only outputs and counts the companies that actually have a review in the reviews table. My goal is to be also combine the results with the 'companies' table, so that companies that don't have a review, still show up in the outputted list in the table, with a '0' for 'ItemSum' and a '0' for count.

问题是,此查询仅输出并计算在评论表中实际具有评论的公司。我的目标是将结果与“公司”表结合起来,这样那些没有评论的公司仍会出现在表格的输出列表中,“ItemSum”为“0”,“a” 0'表示计数。

"ABC Company" "0" "0"
"Best Buy" "35" "3"

“ABC公司”“0”“0”“百思买”“35”“3”

I have researched unions and joins, but so far, I can't find a way to output all the companies in my list, by distinct name and total count.

我已经研究了工会和加入,但到目前为止,我找不到一种方法来输出我的列表中的所有公司,通过不同的名称和总数。

If anyone has any direction on this, I would very much appreciate any advice.

如果有人对此有任何指示,我将非常感谢任何建议。

2 个解决方案

#1

Use query over companies with LEFT JOIN to query for COUNT:

使用LEFT JOIN查询公司查询COUNT:

e.g.

SELECT c.*, COALESCE(x.cnt, 0) 
FROM companies c 
   LEFT JOIN 
   ( 
       SELECT company, COUNT(*) AS cnt 
       FROM reviews 
       GROUP BY company 
   ) x ON x.company = c.company_id 
WHERE c.company LIKE '%...%'

With LEFT JOIN you combine all the records from the left (first) table - here, companies, with records from the second table (here - x). Of course '%...%' is put for example :)

使用LEFT JOIN,您可以将左(第一)表中的所有记录(此处为公司)与第二个表中的记录(此处为-x)组合在一起。当然'%...%'就是举例如:)

#2

1.) You should change the column "Company" on the reviews table to be a foriegn key to company_id on the companies table. It appears to just be by name right now. This will facilitate joins, and allow constraints.

1.)您应该将评论表中的“公司”列更改为公司表上company_id的外键。它现在似乎只是名字。这将促进连接,并允许约束。

2.) You likely want a LEFT OUTER JOIN which will give you all the rows from the first table (in this case the companies)

SELECT *
FROM Companies c
LEFT OUTER JOIN reviews r
ON c.company_id = r.company_id

EDIT: Vassilen's join is correct, not mine. You need to summarize the reviews rows first, instead of matching row for row.

2.)您可能想要一个LEFT OUTER JOIN,它将为您提供第一个表中的所有行(在本例中为公司)SELECT * FROM Companies c LEFT OUTER JOIN review r ON c.company_id = r.company_id编辑:Vassilen的加入是对的,不是我的。您需要首先汇总评论行,而不是匹配行的行。

SELECT c.company, COALESCE(r.total_score,0) as total_score, r.count
FROM Companies c
LEFT OUTER JOIN
( 
    SELECT company_id, SUM(comp_benefits + work_life_balance + job_security + career_growth) as total_score, COUNT(*) as count
    FROM reviews 
    GROUP BY company
) r
ON r.company_id = c.company_id

3.) Do you want the total score to be the sum of all the reviews? As I read it a company with 100 reviews that are poor would beat a company with one good review...

3.)你想要总分是所有评论的总和吗?正如我读到的那样,一家拥有100条评论的公司,只有一个好的评论会打败一家公司......

#1