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.
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
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:
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.
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
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.