SQL nested queries and subqueries

Daphne Comendador Source

i've been having an error with this query and i'm not sure how to fix it. this query is supposed to filter occupations stored in my database to match the volunteer's occupation. please help me fix my query. all the names in this query are correctly spelled, i double checked all the spellings before writing the query here.

the error says "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT (SELECT count(*) FROM occupation_event WHERE event_id='8' AND occupationN' at line 1"

SELECT 
   * 
FROM 
   volunteer_details 
WHERE 
   user_id=73    
AND 
   volunteer_occupation in (
      SELECT 
          occupationName 
      FROM 
          occupation_event 
      WHERE 
          event_id=8 
      OR SELECT (
           SELECT 
               count(*) 
           FROM 
               occupation_event
           WHERE 
               event_id='8' 
           AND 
              occupationName = 'No Occupation Required') > 0 AS need

)

mysqlsql

Answers

answered 1 week ago Gordon Linoff #1

I think the error is the as need at the end. I would write this as:

SELECT vd.*
FROM volunteer_details vd
WHERE user_id = 73 AND
     (vd.volunteer_occupation in (SELECT oe.occupationName FROM occupation_event oe WHERE oe event_id = 8) or
      exists (select 1 from occupation_event oe where event_id = 8 and oe.occupationName = 'No Occupation Required')
     );

comments powered by Disqus