title: Query to generate failed and not appeared for exam
body: <p>Let us say we have tables Student , Subject and Marks Tables.</p>
  
  <p>The structure and data as below </p>
  
  <pre><code>CREATE MULTISET TABLE tmp_work_db.student ,FALLBACK ,
       NO BEFORE JOURNAL,
       NO AFTER JOURNAL,
       CHECKSUM = DEFAULT,
       DEFAULT MERGEBLOCKRATIO
       (
       student_id     integer,  
    student_name      varchar(50) 
  
         )
  PRIMARY INDEX  ( student_id );
  
  insert into tmp_work_db.student
  values(1,'Tim');
  insert into tmp_work_db.student
  values(2,'John');
  insert into tmp_work_db.student
  values(3,'Roy');
  
  
  CREATE MULTISET TABLE tmp_work_db.subject ,FALLBACK ,
       NO BEFORE JOURNAL,
       NO AFTER JOURNAL,
       CHECKSUM = DEFAULT,
       DEFAULT MERGEBLOCKRATIO
       (
       subject_id     integer,  
    subject_name      varchar(50) 
  
         )
  PRIMARY INDEX  ( subject_id );
  
  insert into tmp_work_db.subject
  values(1,'English');
  insert into tmp_work_db.subject
  values(2,'Maths');
  insert into tmp_work_db.subject
  values(3,'Hindi');
  
  
  CREATE MULTISET TABLE tmp_work_db.marks ,FALLBACK ,
       NO BEFORE JOURNAL,
       NO AFTER JOURNAL,
       CHECKSUM = DEFAULT,
       DEFAULT MERGEBLOCKRATIO
       (
       student_id     integer, 
       subject_id     integer,  
       marks      integer 
  
         )
  PRIMARY INDEX  ( student_id,subject_id );
  
  
  insert into tmp_work_db.marks 
  values(1,1,50);
  insert into tmp_work_db.marks 
  values(1,2,100);
  insert into tmp_work_db.marks 
  values(1,3,40);
  
  insert into tmp_work_db.marks 
  values(2,1,50);
  insert into tmp_work_db.marks 
  values(2,2,30);
  </code></pre>
  
  <p>We need to write a query which will generate a report with students who are failed in a particular subject and who not appeared for subject.</p>
  
  <p>I tried below query to get students who are failed in a particular subjects</p>
  
  <pre><code>sel student_name,subject_name,marks from tmp_work_db.student st 
  inner join
  (
  sel student_id,subject_id,marks from tmp_work_db.marks where marks &lt; 35
  ) mrk
  on st.student_id=mrk.student_id
  inner join tmp_work_db.subject sub
  on sub.subject_id=mrk.subject_id;
  </code></pre>
  
  <p>I am not able to get students who are not appeared in a particular subjects.</p>