Hello, question on sql. There are 2 tables in the database. likes and servers . I made a request that should sort the records from servers by the number of likes they set from the likes table (each rating as a separate record in the table, not as a number). Everything works great, except for the moment when a record from the servers does not have a single rating in the corresponding table with ratings, this record is simply not displayed. I'm not saying that the following code should take such a record into account, I just don’t know how to implement it, because not strong in sql.

Here is the query (for formatting the query itself, do not worry, I use ORM and it requires similar syntax):

select s.id, l.target, count(*) as marks from servers s inner join likes l on l.target = s.id group by s.id order by marks

I would be grateful for any help.

  • Replace the internal binding to the left. - Akina
  • ORDER BY marks DESC or ASC - where DESC is from major to minor, and ASC from minor to major. - And

1 answer 1

I recommend to try this:

 select s.id, count(l.id) as marks from servers s left join likes l on l.target = s.id group by s.id order by marks

Pay attention to a few points:

  1. left join - he will take all the records from the servers.
  2. you have group by s.id but in the list of fields there is still l.target not under the aggregate function - so it is impossible, l.target not needed.
  3. count(*) in your case (with the inner join ) normally counts, but with the left join it count(l.id) records for which there were no likes, so with the left join you need count(l.id) or count(l.target) if likes there is no id field.