Sld Posted August 25, 2003 Share Posted August 25, 2003 Hi there dwd\'s (SELECT poll.id,topico,count(*) as votos,unix_timestamp(inicio) as i, unix_timestamp(fim) as f FROM poll_votes,poll WHERE poll_votes.pollid = poll.id and \'$datactual\'>inicio and \'$datactual\'>fim GROUP BY poll_votes.pollid) UNION (SELECT poll.id,topico,0,unix_timestamp(inicio) as i, unix_timestamp(fim) as f FROM poll WHERE \'$datactual\'>inicio and \'$datactual\'>fim and id NOT IN (SELECT poll.id FROM poll,poll_votes WHERE poll_votes.pollid=poll.id and \'$datactual\'>inicio and \'$datactual\'>fim)) LIMIT $pagstart, $max I\'ve got this query and it works fine, in a beta version of mysql in standart nop since it doesnt support NOT IN syntax, can anyone helpme with this? Any other ideias? Tks in advance Quote Link to comment Share on other sites More sharing options...
effigy Posted August 25, 2003 Share Posted August 25, 2003 greetings. i could be wrong, but i do not think not in is the problem. i think the issue is that within not in is a subquery, which the standard versions of mysql do not support. you could run that query separate and loop its results to construct the not in syntax. let me know what you find... i have removed your other post--please do not double post in the forums. thanks... Quote Link to comment Share on other sites More sharing options...
Sld Posted August 25, 2003 Author Share Posted August 25, 2003 Tks dwd, sorry for the double post! The not in will only be available in 4.1 version. Anyway you\'re right, do you have any ideia how can i make this in another way? Quote Link to comment Share on other sites More sharing options...
effigy Posted August 25, 2003 Share Posted August 25, 2003 SELECT poll.id,topico,count(*) as votos,unix_timestamp(inicio) as i, unix_timestamp(fim) as f FROM poll_votes,poll WHERE poll_votes.pollid = poll.id and \'$datactual\'>inicio and \'$datactual\'>fim GROUP BY poll_votes.pollid) UNION (SELECT poll.id,topico,0,unix_timestamp(inicio) as i, unix_timestamp(fim) as f FROM poll WHERE \'$datactual\'>inicio and \'$datactual\'>fim and id NOT IN (SELECT poll.id FROM poll,poll_votes WHERE poll_votes.pollid=poll.id and \'$datactual\'>inicio and \'$datactual\'>fim) LIMIT $pagstart, $max run the query in bold separately. create an array from this result and use the implode function to concat the id\'s; add this into the query; run the query, eg: $poll_ids = // list of polls ids from first query, separated by commas $other_query = SELECT poll.id,topico,count(*) as votos,unix_timestamp(inicio) as i, unix_timestamp(fim) as f FROM poll_votes,poll WHERE poll_votes.pollid = poll.id and \'$datactual\'>inicio and \'$datactual\'>fim GROUP BY poll_votes.pollid) UNION (SELECT poll.id,topico,0,unix_timestamp(inicio) as i, unix_timestamp(fim) as f FROM poll WHERE \'$datactual\'>inicio and \'$datactual\'>fim and id NOT IN ($poll_ids) LIMIT $pagstart, $max Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.