CODEWITHSUNDEEP
sqlitejoinmax
alex.bour
alex.bour

Reputation: 2976

SQLite obtain a MAX from 2 joins

I would like to get some scores from 2 tables (tastings, ratings) join to a first table (wines). 1 wine can own multiples tastings and ratings.

Structure:

table wines
-----------
id 
name

table tastings
-------------
id 
wine_id
date
score

table ratings
-------------
id
wine_id
date
score

Scenario: 1 wine has 2 tastings with different dates and scores, and 3 ratings with different dates and scores.

I want to obtain the wine with last tasting (MAX date) and score, and the last rating (MAX date) and score.

Example data:

Wines:
-----
id: 1
name: Beaulieu Vineyard

tastings:
--------
id: 1
wine_id: 1
date: 2014-01-01
score: 4
-
id: 2
wine_id: 1
date: 2015-02-02
score: 5

ratings:
--------
id: 1
wine_id: 1
date: 2013-04-04
score: 6
-
id: 2
wine_id: 1
date: 2014-05-05
score: 7
-
id: 3
wine_id: 1
date: 2015-06-06
score: 8

So the result wanted is:

Beaulieu Vineyard | Tasting: 2015-02-02 - score: 5 | Rating: 2015-06-06 - score: 8

My current query that works:

SELECT   
wines.winery,  
MAX(tastings.date) AS tasting_date,
MAX(ratings.date) AS rating_date
FROM wines    
LEFT JOIN tastings ON wines.id = tastings.wine_id AND tastings.status=1  
LEFT JOIN ratings ON wines.id = ratings.wine_id AND ratings.status=1  
WHERE wines.status = 1   
GROUP BY wines.id    
ORDER BY wines.winery

The problem is when I try to catch the right score associated to each tasting and each pairing:

SELECT   
wines.winery,  
MAX(tastings.date) AS tasting_date, tastings.score,
MAX(ratings.date) AS rating_date, ratings.score
FROM wines    
LEFT JOIN tastings ON wines.id = tastings.wine_id AND tastings.status=1  
LEFT JOIN ratings ON wines.id = ratings.wine_id AND ratings.status=1  
WHERE wines.status = 1   
GROUP BY wines.id    
ORDER BY wines.winery

The scores are wrong: the results do not display the right score corresponding to the MAX(date).

Upvotes: 1

Views: 53

Answers (1)

CL.
CL.

Reputation: 180200

SQLite can get values from the row that matches a MAX(), but this works only when there is a single MAX().

You have to use subqueries to look up the values:

SELECT winery,
       (SELECT MAX(date)
        FROM tastings
        WHERE wine_id = wines.id
          AND status = 1
       ) AS tasting_date,
       (SELECT score
        FROM tastings
        WHERE wine_id = wines.id
          AND status = 1
        ORDER BY date DESC
        LIMIT 1
       ) AS tasting_score,
       (SELECT MAX(date)
        FROM ratings
        WHERE wine_id = wines.id
          AND status = 1
       ) AS rating_date,
       (SELECT score
        FROM ratings
        WHERE wine_id = wines.id
          AND status = 1
        ORDER BY date DESC
        LIMIT 1
       ) AS rating_score,
FROM wines
WHERE status = 1
ORDER BY winery

Alternatively, look up the maximum dates before doing the grouping:

SELECT wines.winery,
       tasting_date,
       tastings.score,
       rating_date,
       ratings.score
FROM wines
LEFT JOIN (SELECT wine_id AS id,
                  MAX(date) AS tasting_date,
                  score
           FROM tastings
           WHERE status = 1
           GROUP BY wine_id
          ) AS tastings USING (id)
LEFT JOIN (SELECT wine_id AS id,
                  MAX(date) AS rating_date,
                  score
           FROM ratings
           WHERE status = 1
           GROUP BY wine_id
          ) AS ratings USING (id)
WHERE wines.status = 1
ORDER BY wines.winery

Upvotes: 1

Related Questions