Join different tables determined by a control filed

Question

There are three tables A and B1 and B2:

A(id, b_id, control)
B1(id, other)
B2(id, other)

If control = 1, b_id in A is mapping to table B1;
If control = 2, b_id in A is mapping to table B2.

These three classes is like:

@Entity
@Table(name = "A")
public class A {
    @Id
    @Column(name = "id")
    private Integer id;

    private B b;

    @Column(name = "control")
    private Integer control;
} 

@Entity
@Table(name = "B1")
public class B1 {
    @Id
    @Column(name = "id")
    private Integer id;

    @Column(name = "other")
    private Integer other;
}

Class B2 is almost same with B1.

I need get different object B from table B1 or B2 determined by field control in class A. Is it possible to make it with hibernate annotation?

Appreciated for any advice.

`A join B1 on (cotrol = 1...) UNION A join B2 ON(control = 2..)` — sagi, Jul 06 '16 at 10:07

score 0 · Answer 1 · edited May 23 '17 at 12:16

I think you are missing out the way one table refers to another in SQL. What I mean is by the way you have defined your tables there are no foreign keys and no referential integrity secured. I would strongly recommend you reconsidering your implementation and add the respective foreign keys in your A table.

Anyway, if you want to continue like this, you should check out the UNION statement in SQL.

SELECT A.id, B1.others 
FROM A 
INNER JOIN B1 
ON A.control = 1 AND A.b_id= B1.id
UNION
SELECT A.id, B2.others 
FROM A 
INNER JOIN B2 
ON A.control = 2 AND A.b_id= B2.id

Now I have noticed that there is a problem in hql with union statement (not supported I think?) but there are possible other implementations.

Also notice that you have to select the same columns in order to union the two subqueries.

Join different tables determined by a control filed

1 Answers1