troubleshooting Question

Sub select top row in details table using join to master table

Avatar of InnovaDale
InnovaDale asked on
Oracle DatabaseSQL
2 Comments1 Solution965 ViewsLast Modified:
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:

a_text              c_text
---------------------------
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)
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros