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?
GROUP_CONCAT Subqueries
Moderator: General Moderators
Re: GROUP_CONCAT Subqueries
I think you need to have a GROUP BY clause in order to use GROUP_CONCAT.
There are 10 types of people in this world, those who understand binary and those who don't
- kendall
- Forum Regular
- Posts: 852
- Joined: Tue Jul 30, 2002 10:21 am
- Location: Trinidad, West Indies
- Contact:
Re: GROUP_CONCAT Subqueries
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 thoughVladSun wrote:I think you need to have a GROUP BY clause in order to use GROUP_CONCAT.
Re: GROUP_CONCAT Subqueries
Could you post this query?kendall wrote: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 thoughVladSun wrote:I think you need to have a GROUP BY clause in order to use GROUP_CONCAT.
There are 10 types of people in this world, those who understand binary and those who don't
- kendall
- Forum Regular
- Posts: 852
- Joined: Tue Jul 30, 2002 10:21 am
- Location: Trinidad, West Indies
- Contact:
Re: GROUP_CONCAT Subqueries
[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
If I understand you right, then this thread should be useful:
viewtopic.php?f=1&t=94892
viewtopic.php?f=1&t=94892
There are 10 types of people in this world, those who understand binary and those who don't
- kendall
- Forum Regular
- Posts: 852
- Joined: Tue Jul 30, 2002 10:21 am
- Location: Trinidad, West Indies
- Contact:
Re: GROUP_CONCAT Subqueries
Well I'm looking at the topic and the topic itself is confusing to understand.VladSun wrote:If I understand you right, then this thread should be useful:
viewtopic.php?f=1&t=94892
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.
Re: GROUP_CONCAT Subqueries
Better do it in PHP.
There are 10 types of people in this world, those who understand binary and those who don't