title: Coding Inner Join subquery as field in query
body: <p>After looking at example after example of both inner joins and subqueries as fields, I'm apparently not getting some aspect, and I would appreciate help please. I am trying to write one query that must, alas, run in MS Access 2007 to talk to an Oracle database. I have to get values from several different places for various bits of data. One of those bits of data is GROUP_CODE (e.g., faculty, staff, student, alum, etc.). Getting that is non-trivial. I am trying to use two inner joins to get the specific value. The value of borrower category must be the value for my main row in the outer query. Here is what this looks like:
  Patron table      Patron_Barcode table       Patron_Group table
  Patron_id          Barcode                    Patron_Group_iD
  Barcode            Patron_Group_id            PATRON_Group_Code</p>
  
  <p>I want to get the PATRON_GROUP.PATRON_GROUP_CODE. This is only one of 35 fields I need to get in my query. (Yes, that's terrible, but wearing my librarian hat, i can't write the Java program I'd like to write to do this in a snap.) 
  So as a test, I wrote this query:</p>
  
  <pre><code> select PATRON.PATRON_ID As thePatron, 
   (SELECT PATRON_GROUP.PATRON_GROUP_CODE As borrowwerCategory
    FROM (PATRON_GROUP 
          INNER JOIN PATRON_BARCODE ON PATRON_GROUP.PATRON_GROUP_ID = PATRON_BARCODE.PATRON_GROUP_ID
         ) INNER JOIN PATRON ON PATRON_BARCODE.PATRON_ID = thePatron.PATRON_ID
   ));
  </code></pre>
  
  <p>I don't know what I'm doing wrong, but this doesn't work. I've written a fair amount of SQL in my time, but never anything quite like this. What am I doing wrong? </p>
  
  <p>PATRON.BARCODE is the foreign key for the BARCODE table.
  PATRON_BARCODE.PATRON_GROUP_ID is the foreign key for the PATRON_GROUP table. PATRON_GROUP_CODE in PATRON_GROUP is he column value that I need.
  PATRON.BARCODE -> BARCODE.PATRON_GROUP_ID -> PATRON_GROUP.PATRON_GROUP_CODR></p>
  
  <p>The main table, PATRON, will have lots of other things, like inner and outer join to PATRON_ADDRESS, etc., and I can't just do an inner join directly to what I want in my main query. This has to happen in a subquery as a field. Thanks.</p>
  
  <p>Ken</p>