JPA/SQL - Filter table with a composite primary key and perform join on it in 1 query-CodePudding

I have 2 tables that I need to use to get relevant entries based on a few conditions.

Table A (with id being a primary key) is as follows

id  | name  | age  | status
id1 | name1 | age1 |   n
id2 | name2 | age2 |   n
id3 | name3 | age3 |   n
id4 | name4 | age4 |   n

Table B (with id & country being a composite primary key & id being a common column in 2 tables) is as follows

id  | country
id1 | country1
id1 | country2
id2 | country1
id3 | country2
id4 | country1

Now I would like to use a join on these 2 tables to get entries from table A on a condition that entries in table B have country1 but not country2. In other words, from data in 2 tables provided the desired output will be

id  | name  | age  | status
id2 | name2 | age2 |   n
id4 | name4 | age4 |   n

So here id1 is not part of the output since table B has entries with country1 & country2 for id1.

I am using spring data JPA and @Query to perform my queries (on postgres db).

My initial idea was just to chain multiple AND statements like so

select entryA
from tableA entryA
  left join tableB entryB on entryA.id = entryB.id
where entryB.country <> 'country1'
  and entryB.country = 'country2'

However, that didn't work (I still see id1 entry as my final output).

I also tried to use CASE IF END as below

select entryA
from tableA entryA
  left join tableB entryB on entryA.id = entryB.id
where case if entryB.country <> 'country1' then true
           else false
      end
  and entryB.country = 'country2'

But that doesn't work since CASE END is not suppose to come after WHERE (judging from exception I am getting). I tried to move it around, placing in different places within my query but I never managed to make it work.

I also tried to use COUNT in my query within CASE END arg but again couldn't figure out how to organise the query.

The biggest problem I have is I don't know how to put different pieces of the query together in 1 query. I feel like with COUNT, CASE END and left join it is doable. I just cannot put it all together it seems.

So the question I have: well, first of all is what I want to achieve possible to do in 1 query (if not with JPA, then is it possible with native query)? If it is, what am I doing wrong and what is the correct query for this?

CodePudding user response：

select entryA.id as id, (array_agg(entryA.name))[1] as name, (array_agg(entryA.age))[1] as age, (array_agg(entryA.status))[1] as status
from tableA entryA
inner join tableB entryB on entryA.id = entryB.id
group by entryA.id having 'country1'= any(array_agg(entryB.country)) 
and array_length(array_agg(entryB.country),1) = 1

You can use above query for your task.