CODEWITHSUNDEEP
mysqlsql
Somjit
Somjit

Reputation: 2772

getting error : table doesn't exist

I have 3 columns :

  1. college (cname , state , enrollment)
  2. student (sid , sname , gpa , sizehs )
  3. apply (sid , cname , major , decision )

i tried this query :

select j.major , max(j.gpa)
from (select s.* , a.cname , a.major, a.decision 
        from student s join apply a on s.sid = a.sid and decision = 'y') j
group by major

this works. but when i add one more feild to select , it breaks :

select major , sname , max(gpa) , (select avg(gpa) from j) as avg -- trouble
from (select s.* , a.cname , a.major, a.decision 
        from student s join apply a on s.sid = a.sid and decision = 'y') j
group by major
having max(gpa) < avg;

i get an error saying: table j doesn't exist. why does this happen ?
If j doesn't exist then in the first query when i say j.gpa etc , it works.

My aim is to find Majors whose applicant's maximum GPA is below the average , i made a query which works :

select major , sname , student.sid , gpa , 
    (select round(avg(gpa),5) as avg from student join apply 
        on student.sid = apply.sid and decision='Y') as avg
from student join apply on student.sid = apply.sid and decision='Y'
group by major
having max(gpa) < avg

but here , i have to type the join part both in select and from . i was hoping there would be some way to skip having to type the join part two times , so i came up with the (erroneous) query above.

any help would be great.

Upvotes: 1

Views: 873

Answers (2)

Code Lღver
Code Lღver

Reputation: 15603

Try this:

SELECT major, 
       sname, 
       Max(gpa), 
       Avg(gpa) AS avg_gpa 
FROM   (SELECT s.*, 
               a.cname, 
               a.major, 
               a.decision 
        FROM   student s 
               JOIN apply a 
                 ON s.sid = a.sid 
                    AND a.decision = 'y') j 
GROUP  BY major 
HAVING Max(gpa) < avg_gpa;

This is due to j is the alias of the select query and you are using it in the above select query before initialize it. as first it will execute the sub query then select the column from there. And in that this table will not found.

Upvotes: 1

Mehdi
Mehdi

Reputation: 659

try this:

SELECT major, 
       sname, 
       Max(gpa), 
       Avg(gpa) AS avgA 
FROM   (SELECT s.*, 
               a.cname, 
               a.major, 
               a.decision 
        FROM   student s 
               JOIN apply a 
                ON s.sid = a.sid 
                   AND decision = 'y') j 
GROUP  BY major 
HAVING Max(gpa) < avga;

Upvotes: 0

Related Questions