I have the following relationship
Code:
CREATE TABLE "table4"
( "ID" NUMBER(19,0) NOT NULL ENABLE,
PRIMARY KEY ("ID")
)
CREATE TABLE "table1"
( "ID" NUMBER(19,0) NOT NULL ENABLE,
"ID_table4" NUMBER(19,0),
PRIMARY KEY ("ID")
)
CREATE TABLE "table2"
( "ID" NUMBER(19,0) NOT NULL ENABLE,
"BIRTHDAY" DATE NOT NULL ENABLE,
"ID_table4" NUMBER(19,0),
PRIMARY KEY ("ID")
)
CREATE TABLE "table3"
( "ID" NUMBER(19,0) NOT NULL ENABLE,
"BIRTHDAY" DATE NOT NULL ENABLE,
"ID_table4" NUMBER(19,0),
PRIMARY KEY ("ID")
)
And I'm trying to write the following sql select command in using Criteria Query or Predicate or anything like that(I've tryied everything that i know):
Code:
select * from table1
LEFT JOIN
(select max(t2.birthday) from table2 t2
where birthday < '27-OCT-11' OR birthday = null)
ON table1.id_table4 = id_table4
LEFT JOIN
(select max(t3.birthday) from table3 t3
where birthday < '27-OCT-11' OR birthday = null )
ON table1.id_table4 = id_table4
I have to take all that relationship, but a unique birthday with the rule: the most recent birthday
I've tryied to use Predicates but its bring to me all the birthdays, and I want only the most recent.
Sorry by my english, I hope you understood me.
Thanks
Login
FAQ
Search