title: how to change this sql query to sub-query
body: <p>A list showing each product requested on each client stock request. Show client name, product number and quantity requested. Sorted by client name and then product number.</p>
  
  <p>I tried this:</p>
  
  <pre><code>SELECT STOCK_REQUEST.requestNum, STOCK_REQUEST.clientNum, CLIENT.clientName, REQUEST_LIST.productNum, REQUEST_LIST.qtyRequested 
  FROM STOCK_REQUEST, CLIENT, REQUEST_LIST 
  WHERE CLIENT.clientNum = STOCK_REQUEST.clientNum 
  AND REQUEST_LIST.requestNum = STOCK_REQUEST.requestNum 
  ORDER BY CLIENT.clientName AND REQUEST_LIST.productNum
  </code></pre>
  
  <p>I get sth like this:</p>
  
  <pre><code>requestNum  clientNum   clientName  productNum  qtyRequested
  2            2 [-&gt;]     David Liu       4            674
  3            2 [-&gt;]     David Liu       5            66
  1            1 [-&gt;]     Ian Peng        2            45
  5            4 [-&gt;]     James Cameron   3            809
  4            3 [-&gt;]     Mark Moris      1            164
  </code></pre>
  
  <p>But the requirement says we have to use sub-query, so I try to change it to:</p>
  
  <pre><code>SELECT CLIENT.clientName, REQUEST_LIST.productNum, REQUEST_LIST.qtyRequested 
  FROM CLIENT, REQUEST_LIST 
  WHERE CLIENT.clientNum IN (
      SELECT clientNum
      FROM STOCK_REQUEST)
      AND REQUEST_LIST.requestNum IN (
          SELECT requestNum
          FROM STOCK_REQUEST)
  ORDER BY CLIENT.clientName AND REQUEST_LIST.productNum
  </code></pre>
  
  <p>But the result is not what I expect:</p>
  
  <pre><code>clientName  productNum  qtyRequested
  David Liu       4           674
  James Cameron   3           809
  Mark Moris      5           66
  David Liu       2           45
  James Cameron   1           164
  Mark Moris      4           674
  James Cameron   5           66
  Mark Moris      2           45
  Ian Peng        3           809
  James Cameron   4           674
  Ian Peng        1           164
  James Cameron   2           45
  David Liu       3           809
  Ian Peng        5           66
  David Liu       1           164
  Ian Peng        4           674
  Mark Moris      3           809
  David Liu       5           66
  Ian Peng        2           45
  Mark Moris      1           164
  </code></pre>