CODEWITHSUNDEEP
mysqlsqldatabasejoin
Sumit Bhatia
Sumit Bhatia

Reputation: 55

Joining two columns in mysql

I want to add data from table b in table a but unfortunately full outer join do not work in mysql . I have also tried union but it is throwing errors because my statement has group by and order by keyword 

SELECT COUNT( ReviewedBy ) AS TotalReviews, OrganizationId, SUM( Rating ) AS TotalStars, COUNT( Rating ) AS TotalRatings, (
       SUM( Rating ) / COUNT( Rating )
          ) AS AverageRating
        FROM  `tbl_reviews` 
      WHERE ReviewType =  'shopper'
      AND ReviewFor =  'org'
       AND OrganizationId
               IN (

           SELECT OrganizationId
         FROM tbl_organizations
         WHERE CategoryID =79
          )
        GROUP BY OrganizationId
        ORDER BY AverageRating DESC

This is what i'm getting from the above statement

I want to get organizationId 21 data in the result but i'm not getting result because it's not present in 'tbl_review' table click here to see the table b

How can i get Desired result ?

Upvotes: 0

Views: 44

Answers (2)

karina
karina

Reputation: 805

Have you tried:

from organization
left outer join tbl_reviews
on organization.ID = tbl_reviews.organization is

for your where clause? I don't think you need a full outer join in this case... A left outer join should do

Upvotes: 0

dnoeth
dnoeth

Reputation: 60462

You don't need a FULL, but a LEFT join:

SELECT COUNT( ReviewedBy ) AS TotalReviews, o.OrganizationId, 
   SUM( Rating ) AS TotalStars, COUNT( Rating ) AS TotalRatings, 
   (SUM( Rating ) / COUNT( Rating )) AS AverageRating
FROM tbl_organizations AS o
LEFT JOIN `tbl_reviews` AS r
  ON o.OrganizationId = r.OrganizationId
 AND ReviewType =  'shopper' -- conditions on inner table 
 AND ReviewFor =  'org'      -- must be moved to ON
WHERE CategoryID =79
GROUP BY o.OrganizationId
ORDER BY AverageRating DESC

Why don't you use AVG instead of SUM/COUNT?

Upvotes: 1

Related Questions