How to sort a table according to data from another table

Asked

Viewed 240 times

1

I have two tables

Table 1

 create table tb1
(
  cd_tb1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);

Table 2

create table tb2
    (
      cd_tb2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      cd_tb1 int,
      vl_avaliacao int,

      constraint fk_tb1
      foreign key (cd_tb1)
      references tb1 (cd_tb1),
    );

tb2 can have multiple lines with the same code that belong to tb1. I need to make a field average vl_avaliacao and then return the data from tb1 ordered by that average. I wonder if you have any way to do this in Mysql or if I need to return the disordered values and try somehow sort in the application.

php mysql sql database phpmyadmin

  • The table tb1 Do you have any other fields besides id? Ideally you would mount the skeleton of your select and then fix the errors. Note: it is perfectly possible to sort directly in mysql

  • yes, I only put the cd to leave as an example

  • How about using the GROUP BY and ORDER BY options?

  • How to use in this context ?

  • As I said above I need to order the return of tb1 by the average of a field of tb2

2 answers

2


SELECT tb1.cd_tb1, AVG(tb2.vl_avaliacao) FROM 
tb1 LEFT OUTER JOIN tb2 ON (tb1.cd_tb1 = tb2.cd_tb1)
GROUP BY tb1.cd_tb1
ORDER BY tb1.cd_tb1;

Perhaps it might be in your interest to use:

IFNULL(AVG(tb2.vl_avaliacao), 0)

  • I tried to use here but it didn’t work, returned only the cd of tb1 and the AVG of tb2 in incorrect order.

  • Had an error, a , instead of . in (tb1.cd_tb1 = tb2.cd_tb1). Please correct.

0

Complementing the answer of the colleague above (order by average):

SELECT tb1.cd_tb1, AVG(tb2.vl_rating) as Media FROM tb1 LEFT OUTER JOIN tb2 ON (tb1.cd_tb1 = tb2.cd_tb1) GROUP BY tb1.cd_tb1 ORDER BY AVG(tb2.vl_rating) desc;

Browser other questions tagged php mysql sql database phpmyadmin

You are not signed in. Login or sign up in order to post.