CODEWITHSUNDEEP
mysql
Jacob Brunson
Jacob Brunson

Reputation: 1482

MySQL query was working before, now it isn't?

I'm having an odd problem, and I don't have the slightest idea of why it isn't working. I have the following query that I constructed:

SELECT servers.id, servers.name, servers.address, servers.port, servers.up, servers.down, servers.genre, servers.score, servers.version, servers.country, ROUND( AVG( reviews.average ) , 0 ) AS review
FROM servers
INNER JOIN reviews ON servers.id = reviews.server
ORDER BY servers.score DESC

This query was working fine a few weeks ago. It is meant to get many fields from the "servers" table, and the average field from the "reviews" table where the server in the "reviews" table is the same as the id in the "servers" table.

Like I said, this query was working fine before. Yesterday I noticed that a vital part of my site wasn't working, and I figured out that this query is failing.

I've confirmed that is returning exactly 1 row (when, at the moment, it should be returning 4, because there are 4 entries in the "servers" table.) This is what phpMyAdmin gives me when I execute that query:

id    name  address  port   up    down  genre   score   version  country   review
NULL  NULL  NULL     NULL   NULL  NULL  NULL    NULL    NULL    NULL       NULL

Could anybody enlighten me? I've come here as a last resort, because I am stuck.

Upvotes: 0

Views: 144

Answers (1)

Mike Purcell
Mike Purcell

Reputation: 19989

As mentioned in the comments, try changing the INNER JOIN to a LEFT OUTER JOIN which will return servers, regardless if there is a matched row in the review table or not. Also, you didn't post your schema, but double check the reviews.server column in the reviews table, it may be server_id instead. Another issue, you are doing an AVG which is a grouped calculation, but you have no GROUP BY clause, so I would suggest adding it, so your full query should look like:

SELECT servers.id, servers.name, servers.address, servers.port, servers.up, servers.down, servers.genre, servers.score, servers.version, servers.country, ROUND( AVG( reviews.average ) , 0 ) AS review
FROM servers
LEFT OUTER JOIN reviews ON servers.id = reviews.server # might be reviews.server_id
GROUP BY reviews.server
ORDER BY servers.score DESC

More info about GROUP BY functions.

-- Update --

SELECT servers.id, servers.name, servers.address, servers.port, servers.up, servers.down, servers.genre, servers.score, servers.version, servers.country, IFNULL(ROUND(AVG(reviews.average)), 0) AS review
FROM servers
LEFT OUTER JOIN reviews ON servers.id = reviews.server
GROUP BY servers.id
ORDER BY servers.score DESC

Upvotes: 2

Related Questions