GROUP_CONCAT Subqueries
Posted: Wed Feb 11, 2009 9:12 am
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?
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?