Go Back   vb.org Archive > vBulletin 3 Discussion > vB3 Programming Discussions
  #1  
Old 04-09-2009, 08:42 PM
Antivirus's Avatar
Antivirus Antivirus is offline
 
Join Date: Sep 2004
Location: Black Lagoon
Posts: 1,090
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default MySQL - stumped using GROUP_CONCAT result in WHERE clause

Hello, having a little trouble here with a query and hoping someone here knows the answer without having to do a query within the select statement...

My query:
PHP Code:
SELECT COUNT(*) AS threads
    
p.postid AS sc_postidp.sc_typeset
    
GROUP_CONCAT(sg.groupid) AS sc_groupidGROUP_CONCAT(sg.name ORDER BY sg.name ASC SEPARATOR ', ') AS sc_groupname
FROM thread 
AS thread
LEFT JOIN post 
AS p ON (p.postid thread.firstpostid)
LEFT JOIN sc_postgroup AS sc_pg ON (sc_pg.postid p.postid)
LEFT JOIN socialgroup AS sg ON (sg.groupid sc_pg.groupid)
WHERE forumid 33
    
AND sticky 0
    
AND thread.visible 
    
AND sc_groupid IN ('3,5,7'
GROUP BY p.postid
My problem lies within the where clause, with this: AND sc_groupid IN ('3,5,7')

The field `sc_groupid` is produced above using GROUP_CONCAT(), usually this will result in a string like this: '6' however sometimes also like this: '6,8,22,102'

Is this possible to do without a subquery? If I must use a subquery, how would I do so? I'm not too good yet with complex queries , thanks in advance

--------------- Added [DATE]1239318050[/DATE] at [TIME]1239318050[/TIME] ---------------

Another thing i tried was this way, I expected this to work, however it returns no rows, which makes even less sense...

PHP Code:
SELECT COUNT(*) AS threads
    
p.postid AS sc_postidp.sc_typeset
    
GROUP_CONCAT(sg.groupid) AS sc_groupidGROUP_CONCAT(sg.name ORDER BY sg.name ASC SEPARATOR ', ') AS sc_groupname
FROM thread 
AS thread
LEFT JOIN post 
AS p ON (p.postid thread.firstpostid)
LEFT JOIN sc_postgroup AS sc_pg ON (sc_pg.postid p.postid)
LEFT JOIN socialgroup AS sg ON (sg.groupid sc_pg.groupid)
WHERE forumid 33
    
AND sticky 0
    
AND thread.visible 
    
AND sg.groupid IN ('3,5,7'

GROUP BY p.postid
--------------- Added [DATE]1239333280[/DATE] at [TIME]1239333280[/TIME] ---------------

SOLVED - i forgot all about FIND_IN_SET()
that works

PHP Code:
 SELECT COUNT(*) AS threads 
    
p.postid AS sc_postidp.sc_typeset 
    
GROUP_CONCAT(sg.groupid) AS sc_groupidGROUP_CONCAT(sg.name ORDER BY sg.name ASC SEPARATOR ', ') AS sc_groupname 
FROM thread 
AS thread 
LEFT JOIN post 
AS p ON (p.postid thread.firstpostid
LEFT JOIN sc_postgroup AS sc_pg ON (sc_pg.postid p.postid
LEFT JOIN socialgroup AS sg ON (sg.groupid sc_pg.groupid
WHERE forumid 33 
    
AND sticky 
    
AND thread.visible 1  
    
AND FIND_IN_SETsg.groupid'2,3,5,7' ) > 0
GROUP BY p
.postid
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 07:54 PM.


Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2024, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.03274 seconds
  • Memory Usage 2,228KB
  • Queries Executed 11 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (3)bbcode_php
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)navbar
  • (3)navbar_link
  • (120)option
  • (1)post_thanks_box
  • (1)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (1)post_thanks_postbit_info
  • (1)postbit
  • (1)postbit_onlinestatus
  • (1)postbit_wrapper
  • (1)showthread_list
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.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/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_threadedmode.php
  • ./includes/functions_post_thanks.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
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids_threaded
  • showthread_threaded_construct_link
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • fetch_musername
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete