Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2013
    Posts
    2

    Unanswered: Inner join on inner join

    Hi,

    I am writing some Excel VBA code to query an ORACLE database and get some data out for a report. The problem is the structure of the tables that require 2 filters in the same table.

    Lets assume the 2 tables I need, are table1 and table2. They have plenty of columns each, i dont need them all, so i will show the ones that interest me.

    Code:
    table1 (holds all attributes of books - defined by bkd_id)
    -------------
    attribute_type attribute_value bkd_id
    A              5                  1
    B              D                  1
    C            2345               1
    B              D                  2 
    A              7                  2
    C            2100               2
    etc
     
    table2
    ---------------------
    value1 value2
    5         10
    6         20
    7         32
    8         12
    etc     etc
     
    Now what i want, is the following. I want to query table1 for all the attribute_type = 'B' and attribute_value = 'D' and get the value of the attribute_type = 'A' which will be the filter i use in table2 (as value1), to get value2. (I know its a bit complex)
     
    What i would want to end up, is:
     
    value1 value2
    5           10
    7           32
    etc        etc
    So far, I have tried all sorts of things, but no luck. I can get it to work in design mode in Access, but not in the Excel VBA (MSDAORA)

    The bit below was at least supposed to give me the attribute values for attribute type = A in the first table. But it gives me a 'SQL Command not properly ended'. And this isnt even close to the final query i am after but thought I'd start small.


    Code:
    SELECT A.table1.ATTRIBUTE_VALUE FROM table1 AS A INNER JOIN table1 AS B ON A.table1.BKD_ID = B.table1.BKD_ID WHERE B.table1.ATTRIBUTE_TYPE = 'B' AND B.table1.ATTRIBUTE_VALUE = 'D' AND A.table1.ATTRIBUTE_TYPE = 'A'
    Any help, very welcome. If I have missed some important information, let me know and i will add it
    Last edited by MSaxp; 06-12-13 at 10:41.

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hi,

    it seems to be the same error as shown in this post: http://www.dbforums.com/oracle/16689...rly-ended.html

    Just folow the advice given there.

  3. #3
    Join Date
    Jun 2013
    Posts
    2

    Thumbs up

    Thank you very much for your help. I had found the topic you suggested in my search, but didnt think it was applicable to my case. Having another look, I can now see the logic behind joining on another query and also changing the alias referencing.

    Worked fine.

    Much appreciated

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •