vb.org Archive - Problem with threadcount...

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - Problem with threadcount... (https://vborg.vbsupport.ru/showthread.php?t=162627)

Kaitlyn2004

11-13-2007 06:03 PM

Problem with threadcount...

This is the threadcount query in forumdisplay:

Quote:

SELECT COUNT( * ) AS threads, SUM( IF( thread.lastpost >1194966616
AND OPEN <>10, 1, 0 ) ) AS newthread
FROM vb3thread AS thread
WHERE forumid =11
AND sticky =0
AND visible
IN ( 0, 1, 2 )

I have another table, vb3tags ... has tag, and threadid columns. If I want to search multiple tags (AND) I might do something like this (off top of head):

Quote:

SELECT COUNT( * ) AS threads, SUM( IF( thread.lastpost >1194966616
AND OPEN <>10, 1, 0 ) ) AS newthread
FROM vb3thread AS thread, vb3tags AS tags
WHERE forumid =11
AND sticky =0
AND visible
IN ( 0, 1, 2 )
AND tags.threadid = thread.threadid
AND tags.Tag IN ('tag1', 'tag2')
GROUP BY thread.threadid
HAVING COUNT(thread.threadid) = 2

Of course, that doesn't work. Can anyone help me along with this? My method works for everything EXCEPT the counting of threads..

ALL help greatly appreciated

Analogpoint

11-13-2007 07:04 PM

What is it exactly that you want to accomplish?

Kaitlyn2004

11-13-2007 07:20 PM

Take the original count query but only count the threads which have a tag of TAG1 and TAG2, which is from vb3tags that has a threadid column...

Analogpoint

11-13-2007 07:41 PM

Ok, gotcha. I'm not an SQL guru, so I probably won't be of much help. But you should be able to accomplish that with an inner join.

All times are GMT. The time now is 04:51 AM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01065 seconds Memory Usage 1,713KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (2)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (4)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: