MYSQL Select values of sub query result in main query [closed]

My sql query is

 SELECT c_id
      , name
      , stu_id 
      , option_id
      , expiration_status 
      , expiration_date_type 
      , expiration_date_val 
   FROM options 
  WHERE option_id 
     IN 
      ( SELECT  option_id 
          FROM registration WHERE status NOT in ('C','T')
           AND structure = 'C' 
      ) 
  ORDER 
     BY structure
      , option_id ASC

Here i want to select list of option_id which are selected in sub query as a new column in main query

Answer

You can use INNER JOIN to do it

SELECT o.c_id,o.name,o.stu_id ,o.option_id,o.expiration_status , 
 o.expiration_date_type , o.expiration_date_val
FROM `options` o
JOIN `registration` r ON r.option_id=o.option_id AND  r.status NOT in ('C','T')
    AND r.structure = 'C' 
    ORDER BY o.structure, o.option_id ASC

Advertisement

Answer