| Antivirus |
04-09-2009 08:42 PM |
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_postid, p.sc_typeset
, GROUP_CONCAT(sg.groupid) AS sc_groupid, GROUP_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 = 1
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_postid, p.sc_typeset
, GROUP_CONCAT(sg.groupid) AS sc_groupid, GROUP_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 = 1
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_postid, p.sc_typeset
, GROUP_CONCAT(sg.groupid) AS sc_groupid, GROUP_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 = 1
AND FIND_IN_SET( sg.groupid, '2,3,5,7' ) > 0
GROUP BY p.postid;
|
More Information