Geek Answers Handbook

Query without a view

I have two tables flat and usertoflat. I want to get all the data from a flat table, where the condition on the table is usertoflat.

I use JPA, but I did not find the derived table query in jpa below, this is my own 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 a view

 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 result without using a view because I use JPA and I did not find the view in JPA, so if I can get my own SQL query, I will convert it to a JPA request.

+5
source share
2 answers

You can just 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

+4
source

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
+1
source

More articles:


All Articles