Good morning!
I have been working on this issue for the last few days, and I have searched and am looking for advice. When I run the query in SqlYog, I return one row. When I place the code into Hibernate using SQLQuery, I am returning a set of Elections when I should be returning only one.
Here is the SQL code:
Code:
SELECT distinct PollingPlaces.*
FROM PollingPlaces
JOIN ElectionPrecincts ON ElectionPrecincts.pollingPlaceId = PollingPlaces.id
JOIN Elections ON ElectionPrecincts.electionId = Elections.id
JOIN Precincts ON ElectionPrecincts.precinctId = Precincts.id
JOIN Voters ON Precincts.id = Voters.precinctId
JOIN VlAbsenteeBallots ON Voters.id = VlAbsenteeBallots.voterId
WHERE
Elections.electionDate >= CURDATE()
AND Voters.stateVoterId = '09a68'
Here is the hibernate code:
Code:
String sql = "SELECT distinct {pp.*} " +
" FROM PollingPlaces " +
" JOIN ElectionPrecincts ON ElectionPrecincts.pollingPlaceId = PollingPlaces.id " +
" JOIN Elections ON ElectionPrecincts.electionId = Elections.id " +
" JOIN Precincts ON ElectionPrecincts.precinctId = Precincts.id " +
" JOIN Voters ON Precincts.id = Voters.precinctId " +
" JOIN VlAbsenteeBallots ON Voters.id = VlAbsenteeBallots.voterId" +
" WHERE " +
" e.electionDate >= CURDATE()"+
" AND v.stateVoterId = :stateVoterId ";
SQLQuery ballotQuery = s.createSQLQuery(sql.toString());
ballotQuery.addEntity("pp", PollingPlace.class);
ballotQuery.setString("stateVoterId", stateVoterId);
PollingPlace pollingPlaces = (PollingPlace) ballotQuery.uniqueResult();
Why am I getting a set of Elections back, when in the ElectionPrecincts table, each row is unique to do every combination of having a PollingPlaceId, PrecinctId, and ElectionId ??
My table structures are linked like this:
Elections <--> VlAbsenteeBallots <--> Voters
||
ElectionPrecincts <--> Precincts <----------^
||
PollingPlace
Thanks for any advice. Let me know if you need any clarification.
Login
FAQ
Search