Migrating to MySql syntax problem

[PetersWeb]

I'm currently using Version 4.0.20-a and am migrating data from a Sybase Adaptive Server Anywhere V7 database. I've managed to import the data OK, but my problem now is the different sytax on existing queries. I've searched the MySql manual without success on this one and need help!

The site is built in Cold Fusion 4.5 and the current query is:

SELECT (SELECT LIST(Category_desc, ',') AS catlist FROM subscriber_to_category, category where Category.Category_ID = Subscriber_to_category.Category_ID AND subscriber_ID = <cfqueryparam value="#Session.subscriber_ID#" cfsqltype="CF_SQL_INTEGER">) AS listcat, Subscriber_ID, Category_desc, Subscriber_to_category.Category_ID, Active, Subscriber_start, Subscriber_end
FROM Subscriber_to_category, category
WHERE Subscriber_ID = #Session.subscriber_ID# AND Category.Category_ID = Subscriber_to_category.Category_ID
ORDER BY Subscriber_to_category.category_id DESC

This is part of a login script that determines which products the user is subscribed to (they are able to subscribe to more than one).

Please let me know your thoughts!!!

Peter Toby
E: peter@toby.net.au
W:

http://www.toby.net.au

Yahoo!: yellowandblack58
MSN: yellowandblack58@hotmail.com
ICQ UIN: 1237906
AIM: petersweb58

 

[sleipnir214]

I assume that the Sybase LIST() function returns a delimited list of values as a single derived column. For example, in this application, it will return a comma-delimited list of category descriptions.

Unfortunately, MySQL does not support an equivalent to that function. If you can loop through the return in ColdFusion and build that string, your best bet is to simply drop that part of your query. The rest you should be able to use as-is:

SELECT
	category_desc,
	s2c.Category_ID,
	active,
	subscriber_start,
	subscriber_end
FROM
	subscriber_to_category s2c,
	category c
WHERE
	c.category_ID = s2c.category_ID and
	s2c.subscriber_ID = <subscriber ID number>
ORDER BY
	s2c.category_id DESC

Want the best answers? Ask the best questions!

TANSTAAFL!!