Query help - concat a subquery as a single column in parent query

[kaosweaver]

I'm hoping that is what I want...

 

mySQL v4.1

 

Here is the parent query

 

SELECT business.businessid, business.name, city, logo 
FROM business, xbusinesscategorytype 
WHERE business.businessID = xbusinesscategorytype.businessid 
ORDER BY city

 

Each business has categories and I want to have each category the business is part of listed as well, we get the categories by:

 

select categorytype.name 
from xbusinesscategorytype, categorytype 
where categorytype.categoryTypeID=xbusinesscategorytype.categoryTypeID AND businessID=1032

 

What I thought would work would be to do this:

SELECT business.businessid, business.name, city, CONCAT_WS(",",select categorytype.name 
from xbusinesscategorytype, categorytype 
where categorytype.categoryTypeID=xbusinesscategorytype.categoryTypeID AND businessID=business.businessid) as listingDesc, logo
FROM business, xbusinesscategorytype 
WHERE business.businessID = xbusinesscategorytype.businessid 
ORDER BY city

 

An example of the end result would be:

 

1000 | Sears | Tampa | lawn care, appliances, electronics, clothes

1001 | The Gap | Tampa | clothes

1002 | Spensers | St Petersburg | gifts, posters, collectibles

 

and so on.

 

I know I can't be the only/first person with this issue - but I just don't know how to do the right search to find the right solution (some were close, but they dealt with the subquery outside of a column reference, couldn't handle that the subquery returned more than one record, dealt with group by aggregate functions, etc).

 

I would appreciate some help with this, thanks!

K

[aschk]

I'll push you in the right direction:

1) You need to use GROUP BY

2) You need to use GROUP_CONCAT

3) You should specify you joins using ANSI SQL, e.g. JOIN <blah> ON blah.column = another.column, instead of using the WHERE clause.