Jump to content


Photo

Mysql Query > Mysql standart version


  • Please log in to reply
3 replies to this topic

#1 Sld

Sld
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 25 August 2003 - 09:54 AM

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

#2 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 25 August 2003 - 10:36 AM

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...
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#3 Sld

Sld
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 25 August 2003 - 10:55 AM

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?

#4 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 25 August 2003 - 08:02 PM

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


Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users