JOIN question

Hi, I have a question concerning joining three tables together, and
getting the information to spit out correctly. Say I have three
tables:
products
ID Code Name
1 9000 Test Product
categories
ID Name
1 Test Category
2 Another Test
3 Yet another test
productcategories
ID Category Product
1 1 1
2 2 1
3 3 1
Now I want SQL to send back product ID, Code, Product Name, and all
categories it is with. So for example, one row would be:
ID Code Name Categories
1 9000 Test Product Test Category,Another Test,Yet
another test
Here is the SQL I am using. All it is doing is pulling one category
for now. I don't know how to group by and do a concatenation of all
the categories.
SELECT P.ID,Code,P.Name,C.Category FROM products P LEFT JOIN
productcategories PC ON (P.ID=PC.Product) LEFT JOIN categories C ON
(C.ID=PC.Name) GROUP BY P.ID
Any help in this would be appreciated. I am still relatively new to
the complexity of GROUP BYs in SQL.
Thanks for all the help!
JJHi,
I think pure SQL will not be sufficient in this case as far as text
(categories.names) shall be aggregated (concatenated). 'Group by'
returns just one member of the (each) group. If the select list contains
an aggregate function, 'group by' will aggregate over all group
records . So, if a aggregate function e.g. 'concatenateText' would
exist, it could be used here. As far as I know it does not exist.
Therefore a function could be used doing the concatenation.
Then the query would be like:
SELECT P.ID,Code,P.Name, getAllCategories(P.ID) AS AllCategories FROM
products p
The function 'getAllCategories(@.Pid integer)' has to get all Categories
with your query , concatenates the names and returns the concatenated
string.
Hope this helps
Olli
Jimmy Jim schrieb:

> Hi, I have a question concerning joining three tables together, and
> getting the information to spit out correctly. Say I have three
> tables:
> products
> ID Code Name
> 1 9000 Test Product
>
> categories
> ID Name
> 1 Test Category
> 2 Another Test
> 3 Yet another test
> productcategories
> ID Category Product
> 1 1 1
> 2 2 1
> 3 3 1
> Now I want SQL to send back product ID, Code, Product Name, and all
> categories it is with. So for example, one row would be:
> ID Code Name Categories
> 1 9000 Test Product Test Category,Another Test,Yet
> another test
> Here is the SQL I am using. All it is doing is pulling one category
> for now. I don't know how to group by and do a concatenation of all
> the categories.
> SELECT P.ID,Code,P.Name,C.Category FROM products P LEFT JOIN
> productcategories PC ON (P.ID=PC.Product) LEFT JOIN categories C ON
> (C.ID=PC.Name) GROUP BY P.ID
> Any help in this would be appreciated. I am still relatively new to
> the complexity of GROUP BYs in SQL.
> Thanks for all the help!
> JJ|||Thanks for teh reply. Yeah, I tought it was going to be tough with SQL,
and I am using MySQL, so I can't use subqueries like I watned to. I
ended up creating something like a view and using that in PHP/MySQL. Not
as efficient, but all I could do.
Thanks again for the response!
JJ
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!