Page 1 of 1

GROUP_CONCAT Subqueries

Posted: Wed Feb 11, 2009 9:12 am
by kendall
Hey,

I'm trying to formulate an SQL statement for which a subquery that would return a LIMIT return of rows would be GROUP_CONCAT...

[sql]SELECT t.code AS TeacherCode,GROUP_CONCAT((SELECT rw.code  FROM northstar_access_codes AS rw WHERE rw.type = 'readwrite'      LIMIT 5 ) SEPARATOR ',') AS RWCode FROM northstar_access_codes AS t WHERE t.type = 'teacher' LIMIT 1[/sql]

I tried this however I'm not getting the desired result...I keep getting that the query is return more than 1 row...which is what I want so how can I get it to return more than one row and be able to group those rows into a single row.

i tried

[sql]SELECT t.code AS TeacherCode,(SELECT GROUP_CONCAT(rw.code SEPARATOR ',')  FROM northstar_access_codes AS rw WHERE rw.type = 'readwrite'      LIMIT 5 )  AS RWCode FROM northstar_access_codes AS t WHERE t.type = 'teacher' LIMIT 1[/sql]

but its returning more than 5 items in the group...thus seems to be ignoring the LIMIT 5

anyone see an avenue or a better way?

Re: GROUP_CONCAT Subqueries

Posted: Wed Feb 11, 2009 12:08 pm
by VladSun
I think you need to have a GROUP BY clause in order to use GROUP_CONCAT.

Re: GROUP_CONCAT Subqueries

Posted: Wed Feb 11, 2009 12:14 pm
by kendall
VladSun wrote:I think you need to have a GROUP BY clause in order to use GROUP_CONCAT.
tried that as well...What I get using it is more than 5 being grouped into the result... I'm trying another solution though...will let you know how ti turns out...hasnt been sucessful thus far though

Re: GROUP_CONCAT Subqueries

Posted: Wed Feb 11, 2009 1:17 pm
by VladSun
kendall wrote:
VladSun wrote:I think you need to have a GROUP BY clause in order to use GROUP_CONCAT.
tried that as well...What I get using it is more than 5 being grouped into the result... I'm trying another solution though...will let you know how ti turns out...hasnt been sucessful thus far though
Could you post this query?

Re: GROUP_CONCAT Subqueries

Posted: Wed Feb 11, 2009 1:28 pm
by kendall
[sql]SELECT GROUP_CONCAT(r.code SEPARATOR ',') AS RwCodes, (SELECT COUNT(t.code) - 5 FROM northstar_access_codes t WHERE t.type='readwrite' GROUP BY t.type) AS CodesLeft FROM northstar_access_codes rw LEFT JOIN northstar_access_codes r ON r.code = rw.code AND r.type = 'readwrite'WHERE rw.type = 'readwrite'GROUP BY rw.typeLIMIT 5[/sql]

Re: GROUP_CONCAT Subqueries

Posted: Wed Feb 11, 2009 1:42 pm
by VladSun
If I understand you right, then this thread should be useful:
viewtopic.php?f=1&t=94892

Re: GROUP_CONCAT Subqueries

Posted: Wed Feb 11, 2009 1:49 pm
by kendall
VladSun wrote:If I understand you right, then this thread should be useful:
viewtopic.php?f=1&t=94892
Well I'm looking at the topic and the topic itself is confusing to understand.

the thing about this is I'm trying to get x number of rows( i used 5) of items and group those x results rows into 1 grouped item row. thus i'm only returning 1 row result. :dubious:

Re: GROUP_CONCAT Subqueries

Posted: Wed Feb 11, 2009 3:06 pm
by VladSun
Better do it in PHP.