title: Query to select records from multiple tables based on search criteria from UI
body: <p>I am trying to display results based on search criteria from UI. Search is performed in three tables and the tables are as below...</p>
  
  <p><a href="https://i.stack.imgur.com/VzpPD.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VzpPD.png" alt="Student Table"></a></p>
  
  <p><a href="https://i.stack.imgur.com/f1kfc.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/f1kfc.png" alt="Subjects Table"></a></p>
  
  <p><a href="https://i.stack.imgur.com/uWTNH.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/uWTNH.png" alt="Arrears Table"></a></p>
  
  <p>The search criteria in the UI are Name, Number of Arrears, Arrear Subject in or / and condition. When some one enters J in name and click on search it will display student id 1 and 3. If the search criteria is Jill and English it should display all three arrears subjects (Algebra, English and Mathematics) instead of English alone...</p>
  
  <p>Please find my query below: <br></p>
  
  <pre><code>select s.studentId
        ,s.FirstName
        ,s.LastName
        ,s.Department
        ,d.majorSubject
        ,a.Arrearsubject
  from Student s, Department d, Arrear a
  where ((:firstName is NULL OR upper (s.firstName) like upper(:firstName)||'%')
        or (:lastName is NULL OR upper (s.lastName) like upper(:lastName)||'%') and (:arrearSubject is NULL OR a.arrearID :=arrearSubject) 
  and s.studentID = a.studentID and s.studentID = d.studentID;
  </code></pre>
  
  <p>While passing the arrearSubject from UI to data base i will pick up the related arrear ID and do my search based on that. Here in the above query arrearSubject is what user see and enter, and the value to query is just its id.</p>
  
  <p>Table/ Query is in Mainframe/ DB2. I am more into UI/ UX/ Java/ J2EE. Please apologies if there are any mistakes in the query or my question is very basic.</p>