pise pise - 3 months ago 16
MySQL Question

query without derived table

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

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