Wednesday, 24 May 2017

How to get sub-query columns in main query with WHERE EXISTS in PostgreSQL

I am stuck with a query which takes more time in JOIN, I want to use WHERE EXISTS in place of JOIN since as performance wise EXISTS takes less time than it.

I have modified the query and it's executing as per expectation but I am not able to use sub query's columns in my main query

Here is my query

SELECT MAX(st.grade_level::integer) AS grades ,
       scl.sid AS org_sourced_id
FROM schedules_53b055b75cd237fde3af904c1e726e12 sch
LEFT JOIN schools scl ON(sch.school_id=scl.school_id)
AND scl.batch_id=sch.batch_id
AND scl.client_id = sch.client_id
AND sch.run_id = scl.run_id
WHERE EXISTS
    (SELECT t.term_id,t.abbreviation
     FROM terms t
     WHERE (sch.term = t.term_id)
       AND t.batch_id=sch.batch_id
       AND t.client_id = sch.client_id
       AND t.run_id = sch.run_id)
  AND EXISTS
    (SELECT st.grade_level,
            st.sid
     FROM students st
     WHERE (sch.student_id=st.sid)
       AND st.batch_id= sch.batch_id
       AND st.client_id = sch.client_id
       AND st.run_id = sch.run_id)
GROUP BY scl.sid ,
         sch.course_name ,
         sch.course_number,
         sch.school_id

And I am getting this error:

ERROR: missing FROM-clause entry for table "st"
SQL state: 42P01
Character: 29

I have only used one column here just for sample but I have to use more fields from sub query.

My main aim is that how can I achieve this with EXISTS or any alternate solution which is more optimal as performance wise

I am using pg module on Node.js since as back end I am using Node.js.



via abdulbarik

No comments:

Post a Comment