GROUP_CONCAT Subqueries

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

GROUP_CONCAT Subqueries

Post 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?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: GROUP_CONCAT Subqueries

Post by VladSun »

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
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Re: GROUP_CONCAT Subqueries

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: GROUP_CONCAT Subqueries

Post 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?
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Re: GROUP_CONCAT Subqueries

Post 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]
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: GROUP_CONCAT Subqueries

Post by VladSun »

If I understand you right, then this thread should be useful:
viewtopic.php?f=1&t=94892
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Re: GROUP_CONCAT Subqueries

Post 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:
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: GROUP_CONCAT Subqueries

Post by VladSun »

Better do it in PHP.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply