query without derived table

Question

I have two tables flat and usertoflat I want to fetch all data from flat table with where condition on usertoflat table.

I am using JPA but I didn't found derived table query in jpa below is my native sql query.

Table Flat

flat_id | flat_no | status
   1    |   1001  |   1
   2    |   1002  |   1
   3    |   1003  |   1
   4    |   1004  |   1


Table usertoflat

usertoflat_id | Name    | flat_id | status
      1       | ABC     |   1     |   1
      2       | ABC     |   1     |   2
      3       | XYZ     |   2     |   1
      4       | PQR     |   3     |   1


Required output

flat_id | flat_no | Name
   1    |   1001  | ABC
   2    |   1002  | XYZ
   3    |   1003  | PQR
   4    |   1004  | 

Query with derived table

select f.flat_id, f.flat_no, uf.name from flat f left join 
(select * from usertoflat where status  = 1 )
as uf on f.flat_id = uf.flat_id 

How to achieve the same without using derived table, since I am using JPA and I didn't found derived table in JPA so if I can get the native sql query I will convert the same in JPA query.

Answer 1

You can simply make a left join instead of creating a subquery

SELECT
    f.flat_id,
    f.flat_no,
    uf.name
FROM flat f
LEFT JOIN usertoflat uf ON  f.flat_id = uf.flat_id AND uf.status = 1;

See Demo

Or

SELECT
    f.flat_id,
    f.flat_no,
    uf.name
FROM flat f
LEFT JOIN usertoflat uf ON  f.flat_id = uf.flat_id 
WHERE uf.status IS NULL OR uf.status = 1;

See Demo

Answer 2

I think this is what you need?

select f.flat_id, f.flat_no, uf.name from flat f left join 
usertoflat uf on f.flat_id = uf.flat_id and uf.status  = 1