CODEWITHSUNDEEP
mysqlsql
Jake Stainer
Jake Stainer

Reputation: 75

Reference subquery column in outer main SQL

For an online course script

This is the SQL I have written:

  SELECT l.`id` as parent_lesson_id, l.bigImageURL, l.name, 
(SELECT `percent` as percent FROM courses.`lesson_sessions` 
WHERE lesson_id = parent_lesson_id AND user_id = 52461 ORDER BY `percent` DESC LIMIT 1) as percent  
FROM courses.lessons l  
WHERE l.courseID = 1 AND percent > 30 
ORDER by `order` ASC

I am using a subquery because I am returning a single row (which has the highest percentage), and not all of them. If I used LEFT JOIN, I don't know how I would only return rows which have the highest percentage for that parent_lesson_id

A lesson can have many lesson_sessions which each have a percentage

I am getting the following error message when running this query:

#1054 - Unknown column 'percent' in 'where clause'

Problem I am trying to solve:

How to return all lesson rows, where percentage > 30, taking only the highest percentage row related to the lesson from lesson session table (a lesson can have many sessions, each with a different lesson completion percentage).

Basically I want to return all the lessons where the highest percentage is >30

Upvotes: 0

Views: 33

Answers (2)

forpas
forpas

Reputation: 164099

You are trying to use a derived column in the WHERE clause and this is not allowed:

WHERE .... AND percent > 30

percent is a derived column.

MySql allows the use of derived columns in a HAVING clause, so change to this:

SELECT 
  l.id as parent_lesson_id, l.bigImageURL, l.name, 
  (
    SELECT percent FROM courses.lesson_sessions 
    WHERE lesson_id = l.id AND user_id = 52461 
    ORDER BY percent DESC LIMIT 1
  ) percent 
FROM courses.lessons l
WHERE l.courseID = 1 
HAVING percent > 30
ORDER by l.`order` ASC

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94939

If you want to use the maximum percent somewhere else than inside your subquery, then move the subquery from the SELECT clause to the FROM clause. In order to do so, get the maximum by lesson with aggregation.

SELECT 
  l.id as parent_lesson_id, 
  l.bigImageURL, 
  l.name, 
  p.max_percent as percent  
FROM courses.lessons l  
JOIN
(
  SELECT lesson_id, max(percent) as max_percent
  FROM courses.lesson_sessions 
  WHERE user_id = 52461 
  GROUP BY lesson_id
  HAVING max(percent) > 30
) p ON p.lesson_id = l.id;
WHERE l.courseID = 1
ORDER by order ASC;

Upvotes: 1

Related Questions