Combine 3 SQL tables (one that needs a search, one an avg, and one that needs a count)

Question

This is a bit of a monster query that I am close to but falling short of solving I have 3 tables that share some common IDs that I would like to grab and display data from, but not sure if its possible?

My tables:

Members

|MemberID | Fname | Lname
|  1      | foo   | barr
|  2      | bazz  | lorem
(PK MemberID)

MemberRatings

|RatingID | MemberID | RatingValue
|1        | 1        | 5.0
|2        | 1        | 4.4
|3        | 2        | 4.5
|4        | 1        | 4.0
(PK RatingID, FK MemberID-> Members MemberID

Rental

|RentalID | MemberID | RentalDate
|1        | 1        | 2018-06-06
|2        | 1        | 2018-08-08
(PK RentalID, FK MemberID->Members MemberID)

My query:

SELECT
    #member info
    m.MemberID, m.Fname, m.Lname,
    #ratings info (get ready to cast AVG as decimal)
    ra.RatingID, ra.MemberID, CAST(AVG(ra.RatingValue) AS DECIMAL(3,2)),
    #check how many rentals they have
    re.RentalID, re.MemberID
FROM
    #Member MemberID = Rating MemberID
    Members m 
LEFT JOIN 
    MemberRatings ra ON m.MemberID = ra.MemberID
    #Member MemberID = Rental memberID
LEFT JOIN 
    Rental re ON m.MemberID = re.MemberID
    #Where search terms match
WHERE 
    (m.Fname LIKE '%".$membersSearchTerm."%' OR 
     m.Lname LIKE '%".$membersSearchTerm."%')
#Group by memberID to do the AVG
GROUP BY 
    m.MemberID;

CLOSE to the output I am after, but is pulling duplicate column and returning NULL results

Output:

| MemberID | Fname | Lname | RatingID | MemberID | CAST(AVG(ra.RatingValue) AS DECIMAL(3,2)) | RentalID | MemberID 
| 1        | foo   | barr  | 1        | 1        | 4.46 | 1    | 1
| 2        | bazz  | lorem | 2        | 2        | 4.5  | NULL | NULL

Feels like I am joining incorrectly but my knowledge of SQL is very limited and I was trying to do this via LEFT JOIN, is there a better way to output the results as something like this:

| MemberID | Fname | Lname | AVG  | Rentals |
| 1        | foo   | barr  | 4.46 | 2       | 
| 2        | bazz  | lorem | 4.5  | 0       |

I tired adding a RentalID = NOT NULL in the WHERE but that didn't exactly answer the problem either.

Madhur Bhaiya · Accepted Answer · 2018-10-21T07:38:45.840

When you are doing a Group By, you need to ensure that your Select list contains only either aggregated columns (eg: minimum, maximum, average, count etc), and/or the columns which are being grouped upon.

Do Read: SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by

In order to Count the total number of rentals of a member, you will need to use the COUNT() function.

Try the following:

SELECT
  m.MemberID, 
  m.Fname, 
  m.Lname,
  CAST(AVG(ra.RatingValue) AS DECIMAL(3,2)) AS average_rating,
  COUNT(DISTINCT re.RentalID) AS rental_count 
FROM Members m 
LEFT JOIN MemberRatings ra 
       ON m.MemberID = ra.MemberID
LEFT JOIN Rental re 
       ON m.MemberID = re.MemberID
WHERE (
  m.Fname LIKE '%".$membersSearchTerm."%' OR
  m.Lname LIKE '%".$membersSearchTerm."%'
)
GROUP BY m.MemberID, 
         m.Fname, 
         m.Lname

Perfect thanks so much, I learnt I also had to add a distinct to the count (COUNT(distinct re.RentalID) AS rental_count ) and I am getting the results I was after, thanks for the tips in the right direction :) — Ben, Oct 21 '18 at 07:38

Combine 3 SQL tables (one that needs a search, one an avg, and one that needs a count)

1 Answers1