Join 2 MySQL tables with different information in same query

Question

I have searched during 3 days for a solution for this, have tried dozens of queries and none worked so far... :(

I have table users and I am listing users from there that has a colum user_type = 2

So far so good...

Then I have table feedback and I want to put in same query the list of users and the average feedback that each of those users have (knowing that some users have no feedback and, therefore, are not even listed in feedback table).

The only query that returned something so far was this one:

SELECT DISTINCT u.user_id, u.company_name, u.post_code, u.about_company, avg(fd.rating) AS rating FROM users u LEFT JOIN feedback fd ON fd.vendor_id = u.user_id WHERE u.user_type=2 ORDER BY u.user_id ASC 

Now, on table "feedback" there is only one company with feedback for now. But this listing should return ALL users with column user_type=2, which tere's some 20 or so. The problem is that the result is only 1 company (the first of the list when I list only companies) but that's not even the company that has feedback so the "rating" column is then asigned to the wrong company.

SOOO... after all this mess, I hope someone understands what I am looking for and can help me. :)

EDIT1

My users table has these fields: user_id, company_name, post_code, about_company

My feedback table has these fields: feedback_id, job_id, vendor_id (same as user_id on table users), rating

EDIT2

Ok, I was trying to keep this simple! My tables has a LOT more fields than these and my query is actually a LOT more complex since it calculates the distance considering latitude and longitude. Here's the REAL query:

SELECT DISTINCT u.user_id, u.company_name, u.post_code, u.about_company, coalesce(avg(fd.rating),0) AS rating FROM users u LEFT OUTER JOIN feedback fd ON  fd.vendor_id = u.user_id WHERE u.lat!='' AND u.longt!='' AND u.status!=0 AND u.user_type=2 AND SQRT( POW( 69.1 * ( u.lat - 38.7376772 ) , 2 ) + POW( 69.1 * ( -9.1269717 - u.longt ) * COS( u.lat / 57.3 ) , 2 ) ) < 100 ORDER BY u.user_id ASC

Answer 1

Sounds like you need a left outer join. I'm assuming your schema looks something like this:

create table users (user_id int, user_type int);
create table feedback (feedback_id int, user_id int, feedback_value int);

SELECT user_id, avg(feedback_value)
FROM users
    LEFT OUTER JOIN feedback USING(user_id)
WHERE users.user_type=2
GROUP BY user_id

might want to throw a coalesce in there to return a zero vs null

SELECT user_id, coalesce(avg(feedback_value),0) as average_feedback
FROM users
    LEFT OUTER JOIN feedback USING(user_id)
WHERE users.user_type=2
GROUP BY user_id

Here's an example of it working.

mysql> create database test_pedro;
Query OK, 1 row affected (0.00 sec)

mysql> use test_pedro;
Database changed

mysql> create table users (user_id int, user_type int);
Query OK, 0 rows affected (0.02 sec)

mysql> create table feedback (feedback_id int, user_id int, feedback_value int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into users values (1,2), (2,2), (3,3);                                                    
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into feedback values (1,1,4), (2,1,19);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT user_id, coalesce(avg(feedback_value),0) as average_feedback     
FROM users         
    LEFT OUTER JOIN feedback USING(user_id)     
WHERE users.user_type=2     
GROUP BY user_id;

+---------+------------------+
| user_id | average_feedback |
+---------+------------------+
|       1 |          11.5000 |
|       2 |           0.0000 |
+---------+------------------+
2 rows in set (0.00 sec)

mysql>

Is this the expected output?

Here's your updated query with some mods. Specifically removing the DISTINCT and adding GROUP BY to use the AVG() function

SELECT u.user_id,
       u.company_name,
       u.post_code,
       u.about_company,
       Coalesce(Avg(fd.rating), 0) AS rating
FROM   users u
       LEFT OUTER JOIN feedback fd
                    ON fd.vendor_id = u.user_id
WHERE  u.lat != ''
AND u.longt != ''
AND u.status != 0
AND u.user_type = 2
AND Sqrt(Pow( 69.1 * ( u.lat - 38.7376772 ), 2 ) + Pow( 69.1 * ( -9.1269717 - u.longt ) * Cos( u.lat / 57.3 ), 2)) < 100
GROUP  BY u.user_id,
          u.company_name,
          u.post_code,
          u.about_company
ORDER  BY u.user_id ASC