CODEWITHSUNDEEP
javamysqlsqlspring-boot
Ayan Misra
Ayan Misra

Reputation: 93

Not getting data from a table because of null value of foreign key

I am using mysql data table and SQL query for fetching data, I am selecting from two tables joined by one to many and many to one relationship, but if the mapped table is not having any value then I am not getting even the value of the table which I am selecting. But when I am putting foreign key then its working fine and I am data of both table.

@Query("SELECT LD,REC FROM LayerDetails LD JOIN LD.recipeUser REC")
public List getLayerDetails();

This is my SQL query when recipeUser is null then inspite of having value of Layerdetails I am not getting any values.

Upvotes: 0

Views: 792

Answers (2)

Eugen
Eugen

Reputation: 917

I heaved same problem. I solved it by LEFT JOIN instead simple JOIN.


[INNER] JOIN - Returns only records that match in both tables
FULL JOIN - Returns records in both tables. If there is no match, the missing side will contain null.
LEFT JOIN - Returns all records from left table, with the matching records in table right (if available).
RIGHT JOIN - Returns all records from right table, with the matching records in table left (if available).

Explanation Venn diagram

You can view Visual Representation of SQL Joins for more details.

Upvotes: 2

Michael Altenburger
Michael Altenburger

Reputation: 1336

This happens because the JOIN here is an inner join (which is the default if you don't use a specific join). The inner join iterates over tuples of LayerDetails and RecipeUser. However, as the field recipeUser is null in your case, it does not have a tuple for that instance of LayerDetails. So to also find instances of LayerDetails, which have no recipeUser, you would need to use LEFT JOIN instead.

@Query("SELECT LD,REC FROM LayerDetails LD LEFT JOIN LD.recipeUser REC")
public List getLayerDetails();

If you want to have more information on different JOIN possibilities in JPA, you could have a look at https://www.objectdb.com/java/jpa/query/jpql/from for example.

Upvotes: 1

Related Questions