0

please, help me:

I have next tables:

Unit

  • id
  • name

User

  • id
  • name

Rate

  • unit_id
  • user_id

I do not understand how to create correct structure of criteria from SQL:

Code:

SELECT * FROM Unit WHERE id not in (SELECT unit_id FROM Rate WHERE user_id = 55);

I saw this answer. But I do not understand how to make Condition linked to another Table (Entity).

Community
  • 1
  • 1
Yura Buyaroff
  • 1,718
  • 3
  • 18
  • 31
  • Answer you provided is related to Criteria API, not HQL. Switch to Criteria api. Start with `session.createCriteria(Unit.class)` – Antoniossss Feb 26 '14 at 14:31

2 Answers2

1

Looking at your tag, I think you need the Criteria view of your SQL query; so assuming Unit and Rate classes:

// This is the subquery
DetachedCriteria subquery = DetachedCriteria.forClass(Rate.class)
    .add(Restrictions.eq("user_id", 55))
    .setProjection(Projections.id())

// This corresponds to (SELECT * FROM Unit WHERE id not in (subquery))
Criteria criteria = session
    .createCriteria(Unit.class)
    .add(Subqueries.notIn("id", subquery));
Pang
  • 9,564
  • 146
  • 81
  • 122
Luca Basso Ricci
  • 17,829
  • 2
  • 47
  • 69
0

I don't know that IN would be the most appropriate in this case. Try an inner join to link up the tables:

SELECT * FROM Unit INNER JOIN Rate ON Rate.unit_id = Unit.id 
    WHERE Rate.user_id = 55
mydoglixu
  • 934
  • 1
  • 7
  • 25