I'm currently trying the following query:

SELECT pg.name, pg.id
FROM PollGroup pg, Poll p
WHERE
p IN elements(pg.polls)
AND (
pg.owner = :usr
OR :usr IN elements(pg.pollCreators)
OR p.owner = :usr
OR :usr IN elements(p.editors)
)
ORDER BY pg.name

A PollGroup has a set of Polls. Every Poll and PollGroup has one single User in the property "owner". A PollGroup has a set of users as "pollCreators", a Poll has a similar set named "editors".

I'm trying to select the name and id of every poll group that a User is associated with, either as owner, pollCreator or editor. The above query seems to work, but I'm getting double entries for the case where a user owns both a PollGroup and a contained Poll. How can I work around that? I tried using "SELECT DISTINCT pg.name, pd.id ...", but that gave me a QueryException about expecting an aggregate function.

Any idea on how I can make this query give me only distinct entries? It's probably an easy solution somewhere, but I'm really stuck at the moment.

Thanks
Carl-Eric

you could try using the "group by" syntax

> you could try using the "group by" syntax

Thanks - I tried adding GROUP BY pg.id to the above query, but then I get the following JDBC error through:

Could not execute query: ERROR: Attribute pollgrou0_.name must be GROUPed or used in an aggregate function

I can't group by pg.name, though, because that field is not necessarily unique.

Carl-Eric

Huh!!?? Are you sure? That should work perfectly.

The corrrect query is:



or, probably even better:

Errrmm. Rewriting the whole damn thing complete with the DISTINCT seems to have fixed it. The SELECT DISTINCT does indeed work. How embarrassing. I remember now: I used DISTINCT (pg.name, pg.id), which doesn't seem to work.



Ah, these look interesting. I'll have to read some more about joins, I don't seem to always understand them correctly. (Any good suggestions about further reading?)

I tried the second query - it works, but it also gives me duplicate rows. Shouldn't the left join prevent that? Or am I only showing my ignorance of joins here?

Thanks a lot!
Carl-Eric

Just pick up a book that covers ANSI standard SQL (O'Reilly has a good one)



Oh, you may still need the DISTINCT keyword.