.jsp and MySQL expert help needed

[Homerboy]

I'm trying to create some homebrew polls for my website (see DUFA in sig below).
However I've run into a problem in that I can not figure out a way to get ONLY the polls they have not voted in as of yet to display.

My tables are set up as follows:

poll_info: poll_id, timestamp, question
poll_questions: question_id, poll_id, answer
poll_responses: poll_id, answer_id, team_id

As a vote is cast the poll_responses adds a row adding which poll they voted in, what their answer was (based off question_id from the poll_questions table) and inserts their team id.

I can not come up with a working query though to only display the poll_id for those WHERE team_id != <current GM logged in>. I can use the != but then it displays a row for each vote that is not theres on that poll.

IE: If you have not voted in poll_id = 2, but 5 other GMs have, it will show that poll listed 5 times (as there are 5 rows with poll_id = 2 and NOT your team_id

I'm just having a painful mental block on this... driving me nuts. suggestions?
I will happily share whatever code you want to see.

 

[lozina]

does team_id represents the person logged in?

if so, try this:

select a.* from poll_questions a where not exists (select 1 from poll_responses b where a.poll_id = b.poll_id and b.team_id = <INSERT_YOUR_TEAM_ID> )

replace that team_id check with the proper login check if that's not what team_id is supposed to be...

 

[Homerboy]

SEVERE: Servlet.service() for servlet jsp threw exception
java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1



For now I just hard coded the '0' in there as to eliminate a notehr possible error spot

 

[notfred]

What are you trying to do? Show all the polls that another team has voted in, but you haven't?
select distinct poll_id from poll_responses where team_id != you;

You don't make it clear what you're trying to do though, so that might not be what you want.

 

[Homerboy]

Originally posted by: notfred
What are you trying to do? Show all the polls that another team has voted in, but you haven't?
select distinct poll_id from poll_responses where team_id != you;

You don't make it clear what you're trying to do though, so that might not be what you want.

I tired to make it clear... it's hard to describe I guess.

I did similar to your query before... but not the "distinct"
does that eliminate the duplicate poll_id as there could be 1 poll answered by 20 GMs but not you, returning 20 rows

 

[notfred]

Originally posted by: Homerboy

Originally posted by: notfred
What are you trying to do? Show all the polls that another team has voted in, but you haven't?
select distinct poll_id from poll_responses where team_id != you;

You don't make it clear what you're trying to do though, so that might not be what you want.

I tired to make it clear... it's hard to describe I guess.

I did similar to your query before... but not the "distinct"
does that eliminate the duplicate poll_id as there could be 1 poll answered by 20 GMs but not you, returning 20 rows

That's the whole point of "distinct", it only selects rows that are distinct from one another. Therefore - no duplicates.

 

[Homerboy]

Hmmm I think this "distinct" will work... I gotta toy with it a bit.
I think it will require me to put a dummy record into the poll_responses as each new poll is added to poll_info because if there is no vote on the poll, then it wont show up on the GMs list. And there can't be any votes unless it shows up on their list...

 

[Homerboy]

That's the whole point of "distinct", it only selects rows that are distinct from one another. Therefore - no duplicates.

I realize that.... now... /end Frank Drebbin

I was unfamilair with "distinct" coulda cleared up A LOT of headbanging.

 

[lozina]

Originally posted by: Homerboy
SEVERE: Servlet.service() for servlet jsp threw exception
java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1



For now I just hard coded the '0' in there as to eliminate a notehr possible error spot

according to that line you forgot to close parenthesis on the inner select (edit: I know that SQL works in Oracle and I'm pretty sure there's nothing in there that MySQL doesn't like)

 

[Cerebus451]

Originally posted by: Homerboy
Hmmm I think this "distinct" will work... I gotta toy with it a bit.
I think it will require me to put a dummy record into the poll_responses as each new poll is added to poll_info because if there is no vote on the poll, then it wont show up on the GMs list. And there can't be any votes unless it shows up on their list...

This still won't work because if GMs 2 and 5 have voted on a poll, then there will be a row in the table that is not GM 2, and that poll will get returned. lozina's query would work, but sub-selects were only implemented as of version 5 of MySQL, and even then not completely, which could be the reason for your error. This query should get you what you want without using a subquery:
SELECT pi.poll_id, timestamp, question, pr.poll_id
FROM poll_info pi
LEFT JOIN poll_responses pr ON pi.poll_id = pr.poll_id AND pr.team_id = <current GM logged in>
WHERE pr.poll_id is null

This should get you the list of polls in poll_info for which there is no response for the given team_id in poll_responses. Haven't tested this to be certain, but looks good from here.