Hi,
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:
[quoted text, click to view] > 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
