Many to many

Question

I have three tables:

Products:

Category

and a simple MtM table to connect them

For reporting purposes, I was wondering if it is possible to create a field with the concatenation of all categories to which the element belongs. For example, if Item ID = 1 will belong to the categories ID = 1 and ID = 2; I could make a selection in Items and get the "Categories" field with the value "Schuhe"; Hemde

Is this possible when using only SQL?

The best I could come up with

SELECT Items.*, Categories.CategoryName FROM (CategoryItemAffinities INNER JOIN Categories ON CategoryItemAffinities.CategoryID = Categories.ID) INNER JOIN Items ON CategoryItemAffinities.ItemID = Items.ID; 

But this obviously gives more than one result per element

[edit] Just to indicate, ms access is just a db engine, I don't use access forms / reports, etc. As such. I need this for a C # application

Answer 1

Jet DB does not contain an aggregate function for string concatenation. You can create your own function to do what you need. First, you need your data with itemID and STRING for this category:

yourTableOrQueryName

then write a custom function as follows:

 Public Function getCats(theID) Dim rs As Recordset Dim db As Database Dim qstr As String Dim returnString As String qstr = "SELECT category FROM yourTableOrQueryName WHERE itemID = " & theID Set db = CurrentDb Set rs = db.OpenRecordset(qstr) Do While Not rs.EOF returnString = returnString & ", " & rs!Category rs.MoveNext Loop getCats = Mid(returnString, 3, Len(returnString) - 2) End Function 

Then your request looks like this:

 SELECT itemID, getCats([itemID]) AS catList FROM yourTableOrQueryName GROUP BY itemID; 

This query can be optimized, so it does not run several times for each identifier, inserting the selected unique query that just gets your unique identifiers before you run the getCats () function if you are in the millisecond trim, but I leave it to you.

Results:

Answer 2

You can scroll through the recordset with code to display the search results, but this will require either VBA or C #. Access does not support CTE, like SQL Server, so it removes another very good option. This may be possible using a pivot table and concatenation of the resulting fields. It would be difficult, but I believe that this can be done.

Create a crosstab query as such ...

 TRANSFORM First(Categories.CategoryNar) AS FirstOfCategoryNar SELECT MtM.ItemID FROM Categories INNER JOIN MtM ON Categories.ID = MtM.CategoryID GROUP BY MtM.ItemID PIVOT MtM.CategoryID; 

Then create a query based on a crosstab query like this ...

 SELECT Crosstab.ItemID, [1] & [2] & [3] AS ConcatField FROM Crosstab; 

The numbers are category identifiers.

Hope this helps.