I am trying to select fields from tables on both sides of a many-to-many relationship. Here would be a sample relationship among the three tables:
leftTable - 1 record: pk = 1, text1 = "hello world"
rightTable - 2 records: pk = 1, text1 = "foo" .... pk = 2, text1 = "bar"
midTable - 2 records: pk = 1, a_fk = 1, c_fk = 1, date1 = [yesterday] ... pk = 2, a_fk = 1, c_fk = 2, date1 = [today]
My attempt at the query is attached. I get an Oracle error stating that "a.pk is an invalid identifier". If I move that filter out of my sub query's where clause, I only get 1 record returned for the entire record set where I should get hundreds.
I am trying to obtain the following output:
hello world bar
How can I properly create the sub query while binding the one row returned to the parent query?
select a.text1 as a_text
, c.text1 as c_text
from leftTable a
, midTable b
, rightTable c
where a.pk = b.a_fk
and c.pk = b.c_fk
and b.pk = (select pk from (select z.pk, row_number()over(order by z.date1 desc) m from midTable z where z.a_fk = a.pk) where m = 1)