CODEWITHSUNDEEP
javasqloracle-databasespring-data-jpa
marc
marc

Reputation: 29

SQL Query OneToMany Filtering with child entity but returning parent

Hey I need some help with an SQL Query I just can't figure out. I got these two Tables with an OneToMany/ManyToOne relation:

create table Author (
"UUID" RAW(16) DEFAULT NULL NOT NULL ENABLE,
"name" VARCHAR2(255 CHAR) NOT NULL ENABLE,
"processed" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE,
CONSTRAINT "Author_P" PRIMARY KEY ("UUID"),
)

create table Book (
"UUID" RAW(16) DEFAULT NULL NOT NULL ENABLE, 
"name" VARCHAR2(255 CHAR) NOT NULL ENABLE,
"release" TIMESTAMP(6),
"AUTHOR_ID" RAW(16) NOT NULL,
 CONSTRAINT Book_P PRIMARY KEY (UUID),
        CONSTRAINT Book_F FOREIGN KEY (KAFKA_BEZIEHUNG_ID) REFERENCES "Author" ("UUID")
)

Now I got this select query to get me all Books that have been written before the current date:

(I'm doing this in Java JPA via the Query Annotation)

@Query(Select b from Book b where b.release < current_timestamp)
List<Book> findAllBooksBeforeToday();

In this case I am returning all the books, but what I what instead is:

  1. returning only the authors and without duplicates
  2. Additional I only want to return the authors where the "processed" value is false.

I can achieve the first by doing this or atleast I thought but thats not quite working:

@Query(Select b.author_id from Book b where b.release < current_timestamp)
HashSet<Author> findAllBooksBeforeToday();

The second condition is pretty simple like this:

@Query(Select a from author a where a.processed = false)

So my question is now, how do I and maybe even can I combine these two queries in one query that selects from the book table but only returns the author that are processed = false?

Upvotes: 0

Views: 370

Answers (2)

John D
John D

Reputation: 1637

I'm assuming Book.AUTHOR_ID matches Author.UUID

select distinct b.author_id 
from Book b inner join Author a on a.UUID = b.AUTHOR_ID
where a.processed = 0 and b.release < current_timestamp

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142958

I don't know Java, but - queries that do what you want (at least, how I understood it) are

-- distinct authors (i.e. no duplicates)
select distinct a.name
from author a;

-- "false" processed authors
select distinct a.name
from author a
where a.processed = 0;

-- join books and "false" processed authors
select b.name as book_name,
       a.name as author_name
from book b join author a on a.uuid = b.uuid
where a.processed = 0;

Upvotes: 0

Related Questions