intellisource
asked on
how to use GROUP_CONCAT in a basic join select, with AS too.
hi again,
many thanks to angellll for the tip - only one problem now - i do not know what the hell to do with it in my situation! a cross table select, as follows gives errors and i do not understand why and what needs to change. on top of that i need to have the IF() field selected AS concatenated too - but i am entirely stumped with this new information! :(
and googling for a result in this context - does not seem to turn anything up either.
the reference does not say much regarding this context either. -_-
many thanks to angellll for the tip - only one problem now - i do not know what the hell to do with it in my situation! a cross table select, as follows gives errors and i do not understand why and what needs to change. on top of that i need to have the IF() field selected AS concatenated too - but i am entirely stumped with this new information! :(
SELECT
36_serviceattributegroups.text_AttributeGroupDescription,
36_serviceattributegroups.bigint_OwnerKeyID,
3_serviceattributes.text_AttributeDescription,
GROUP_CONCAT(
36_serviceattributegroups.bigint_AttributeIncludedID
SEPARATOR ", "),
#GROUP_CONCAT(
# IF(
# 36_serviceattributegroups.bigint_AttributeIncludedID,
# (SELECT text_AttributeDescription FROM 3_serviceattributes WHERE bigint_AttributeID = 36_serviceattributegroups.bigint_AttributeIncludedID),
# NULL
# ) AS text_AttributeParentDescription
# SEPARATOR ", "),
3_serviceattributes.bigint_AttributeServiceID
FROM
3_serviceattributes,
36_serviceattributegroups
GROUP BY
36_serviceattributegroups.bigint_OwnerKeyID
WHERE
3_serviceattributes.bigint_AttributeServiceID = 1 AND
3_serviceattributes.bigint_AttributeID = 36_serviceattributegroups.bigint_OwnerKeyID;
this returns#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE 3_serviceattributes.bigintwhat am i doing wrong? and the commented part needs to be included as well - that returns the following error in PMA:_Attribute ServiceID = 1 AND 3_serviceattribu' at line 20
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS text_AttributeParentDescrihelp please!!!! i cannot seem to get this function to work in this context :/ption SEPARATOR ", "), 3_serviceattributes.big' at line 13
and googling for a result in this context - does not seem to turn anything up either.
the reference does not say much regarding this context either. -_-
ASKER
lol damnit - i meant this post.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
oki thankyouuuu! XD
been an awesome lot of help since my tired mind does not want to go further :P
been an awesome lot of help since my tired mind does not want to go further :P
ASKER
ummm sighs...
the query:
the query:
SELECT
36_serviceattributegroups.text_AttributeGroupDescription,
36_serviceattributegroups.bigint_OwnerKeyID,
3_serviceattributes.text_AttributeDescription,
GROUP_CONCAT(
36_serviceattributegroups.bigint_AttributeIncludedID
SEPARATOR ', '),
GROUP_CONCAT(
IF(
(36_serviceattributegroups.bigint_AttributeIncludedID > 0),
(SELECT text_AttributeDescription FROM 3_serviceattributes WHERE bigint_AttributeID = 36_serviceattributegroups.bigint_AttributeIncludedID),
NULL
) AS text_AttributeParentDescription
SEPARATOR ', '),
3_serviceattributes.bigint_AttributeServiceID
FROM
3_serviceattributes,
36_serviceattributegroups
WHERE
3_serviceattributes.bigint_AttributeServiceID = 1 AND
3_serviceattributes.bigint_AttributeID = 36_serviceattributegroups.bigint_OwnerKeyID
GROUP BY
36_serviceattributegroups.bigint_OwnerKeyID;
still gives the folllowing error:#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS text_AttributeParentDescri:/ption SEPARATOR ', '), 3_serviceattributes.big' at line 13
ASKER
ah HAH!!!
:D took a break and did some excersizes, came back and got it working with the context as follows:
thanks again for your help in resolving this, angellll! :)
:D took a break and did some excersizes, came back and got it working with the context as follows:
SELECT
36_serviceattributegroups.text_AttributeGroupDescription,
36_serviceattributegroups.bigint_OwnerKeyID,
3_serviceattributes.text_AttributeDescription,
GROUP_CONCAT(
36_serviceattributegroups.bigint_AttributeIncludedID
SEPARATOR ', ') AS bigint_AttributeIncludedID,
GROUP_CONCAT(
IF(
(36_serviceattributegroups.bigint_AttributeIncludedID > 0),
(SELECT text_AttributeDescription FROM 3_serviceattributes WHERE bigint_AttributeID = 36_serviceattributegroups.bigint_AttributeIncludedID),
""
)
SEPARATOR ', ') AS text_AttributeParentDescription,
3_serviceattributes.bigint_AttributeServiceID
FROM
3_serviceattributes,
36_serviceattributegroups
WHERE
3_serviceattributes.bigint_AttributeServiceID = 1 AND
3_serviceattributes.bigint_AttributeID = 36_serviceattributegroups.bigint_OwnerKeyID
GROUP BY
36_serviceattributegroups.bigint_OwnerKeyID;
i guess a long day's development does take it out of you! lol :)thanks again for your help in resolving this, angellll! :)
ASKER