0

I have a query like this -

SELECT e.id, 
       ( (SELECT ABS((SELECT YEAR(NOW()) - YEAR(ud.dob) 
                      FROM   user_detail ud 
                      WHERE  ud.userid = 49) - AVG(avgr.abc 
                                                   + (SELECT YEAR(NOW()) - YEAR(ud.dob) 
                                                      FROM   user_detail ud 
                                                      WHERE  ud.userid = 49))) AS VALUE 
          FROM   (SELECT YEAR(NOW()) - YEAR(ud.dob) AS abc 
                  FROM   user_detail ud 
                  WHERE  ud.userid = (SELECT ei.interested_user 
                                      FROM   event_interest ei 
                                      WHERE  ei.eventid = e.id 
                                             AND ei.approvalstatus = 'Approve') 
                  UNION 
                  SELECT YEAR(NOW()) - YEAR(ud.dob) AS abc 
                  FROM   user_detail ud 
                  WHERE  ud.userid = (SELECT ei.invited_user 
                                      FROM   event_invite ei 
                                      WHERE  ei.eventid = e.id 
                                             AND ei.acceptance = 'Accept')) AS avgr) 
         + (SELECT IFNULL(( 3959 * ACOS(COS(RADIANS(22.6979425)) * COS(RADIANS(latitude)) * COS(RADIANS(longitude) - RADIANS(75.8597305)) + SIN(RADIANS(22.6979425)) * SIN(RADIANS(latitude))) ), 0)
            FROM   address a 
            WHERE  a.latitude != '' 
                   AND a.longitude != '' 
                   AND e.event_address = a.id) ) AS dest 
FROM   event e 
WHERE  ( ( e.maximumattendeesallow > ( (SELECT COUNT(*) AS cnt 
                                        FROM   event_interest 
                                        WHERE  eventid = e.id 
                                               AND approvalstatus = 'Approve') 
                                       + (SELECT COUNT(*) AS cnt 
                                          FROM   event_invite 
                                          WHERE  eventid = e.id 
                                                 AND acceptance = 'Accept') ) ) 
          OR ( e.maximumattendeesallow = 0 ) ) 
       AND EXISTS (SELECT id 
                   FROM   address a 
                   WHERE  latitude != '' 
                          AND longitude != '' 
                          AND e.event_address = id 
                          AND IFNULL(( 3959 * ACOS(COS(RADIANS(22.6979425)) * COS(RADIANS(latitude)) * COS(RADIANS(longitude) - RADIANS(75.8597305)) + SIN(RADIANS(22.6979425)) * SIN(RADIANS(latitude))) ), 0) < 100)
       AND NOT EXISTS (SELECT eventid 
                       FROM   event_interest 
                       WHERE  e.id = eventid 
                              AND approvalstatus != 'InterestExpressed' 
                              AND interested_user = 49) 
       AND NOT EXISTS (SELECT eventid 
                       FROM   event_invite 
                       WHERE  eventid = e.id 
                              AND invited_user = 49);

This query giving me error -

Error Code: 1054
Unknown column 'e.id' in 'where clause'

Why its not allowing subquery to use e.id in order by clause.

Kermit
  • 33,827
  • 13
  • 85
  • 121
Aamir
  • 738
  • 2
  • 17
  • 41
  • 1
    Please format your query. – Gordon Linoff Mar 26 '14 at 11:25
  • I have seen this post - http://stackoverflow.com/questions/153598/unknown-column-in-where-clause which says where clause e.id first evaluate, how i can rewrite this query so later one evaluate first. – Aamir Mar 26 '14 at 16:22

2 Answers2

0

In the query, you have used E.ID. change it to "e.id" and execute. This time you won't get that error.

shankar kannappan
  • 1
  • 1
0

In fourth select clause, that is after AND NOT EXISTS(Select.., . In where clause you mentioned as WHERE e.id =eventid , instead of this , write as WHERE eventid = e.id . This will resolve your error.

shankar kannappan
  • 1
  • 1