0

I have 3 tables.

Table A - pk_id, FK_B_Id(foreign key of Table B),...
Table B - pk_id, name....
Table C - pk_id, FK_A_Id(foreign key of Table A), name...

I’m getting search result based on Table A. In the table A records either associate with table B or Table c.In the result set, I’m showing name. How to sort search result based on the name?

Darshan Lila
  • 5,772
  • 2
  • 24
  • 34
Roshan
  • 177
  • 2
  • 9

1 Answers1

0

i could find a solution for my problem, i would like to share it with everyone.

I added a new property("name") with formula to Table A hbm.xml file. Then used this property to sort.

This is my property that i added to Table A hbm file.

<property name="name" formula="(SELECT COALESCE((SELECT t.NAME FROM TABLE_B t WHERE t.PK_ID = FK_TABLE_A),(SELECT n.NAME FROM TABLE_C n WHERE n.FK_TABLE_A=PK_ID) ,'') FROM DUAL)"/>
Roshan
  • 177
  • 2
  • 9