CODEWITHSUNDEEP
sqlhql
eskaev
eskaev

Reputation: 1128

Eliminate subquery in the FROM clause

The tagging table has 3 columns: id (the primary key), tag, and resource.

I want to select the tags that are associated with at least 3 resources. A resource can be associated several times with the same tag, so a single GROUP BY is not enough.

My current SQL query is the following:

SELECT tag FROM
(SELECT resource, tag FROM tagging GROUP BY resource, tag) AS tagging
GROUP BY tag HAVING count(*) > 2;

I need to convert this request in HQL, and HQL does not accept subqueries inside the FROM clause.

Is there a (fast) way to do the same thing without using a subquery, or with a subquery in the WHERE clause?

Thank you

Upvotes: 2

Views: 219

Answers (1)

Martin Smith
Martin Smith

Reputation: 453736

To find tags that are associated with more than 2 different resources you can use

SELECT tag
FROM   tagging
GROUP  BY tag
HAVING count(DISTINCT resource) > 2;

Upvotes: 2

Related Questions