SQL Server SELECT composite table

Question

I have the below query:

SELECT  p.id as prod_id, * FROM products AS p 
        LEFT JOIN Product_UPC AS UPC ON UPC.ProductID = p.id
        LEFT JOIN Brands AS b ON p.brand = b.id
        LEFT JOIN productCategoryLink AS c ON c.ProductID = p.id 
        WHERE (p.id = '$this->prod_id')

A product can be assigned to multiple categories and therefore I have a composite table consisting of product and category IDs. I want to amend the above query so that it only brings out one row of data as at the moment it bring out multiple depending on how many categories there are in the composite table. I would like to somehow have the rows of category IDs brought out and added to the one row.

eg.

id | name | desc | category1| category2| category3 | price

Is this possible? If so how?

you have fixed count of categories? If yes how many? – triclosan Nov 18 '11 at 12:25 — triclosan, Nov 18 '11 at 12:25
I do'nt have a fixed number of cats, its unlimited. – LeeTee Nov 18 '11 at 12:44 — LeeTee, Nov 18 '11 at 12:44
I've retagged your question after your comment ;) – Marco Nov 18 '11 at 12:44 — Marco, Nov 18 '11 at 12:44

score 1 · Accepted Answer · answered Nov 18 '11 at 12:27

1

You could try this to obtain categories in one column (you can split them later):

SELECT  p.id as prod_id, * FROM products AS p
        GROUP_CONCAT(c.category), 
        LEFT JOIN Product_UPC AS UPC ON UPC.ProductID = p.id
        LEFT JOIN Brands AS b ON p.brand = b.id
        LEFT JOIN productCategoryLink AS c ON c.ProductID = p.id 
        WHERE (p.id = '$this->prod_id')

If you want to get all products, change last row with this

    GROUP BY p.id

answered Nov 18 '11 at 12:27

Marco

56,740
14
129
152

Sorry, Ive been an idiot, its not MySQL its mssql. Dont think Group_concat works for this. – LeeTee Nov 18 '11 at 12:37
@LeeTee: best [link](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005) I've found... – Marco Nov 18 '11 at 12:44
Ive amended the query as in the link supplied but it does not work. Not sure what to put where. My amended query is:SELECT p.id as prod_id, * FROM products AS p CROSS APPLY (SELECT categoryID + ',' FROM information_schema.columns AS intern WHERE extern.productCategoryLink = intern.productCategoryLink FOR XML PATH('') ) pre_trimmed (column_names) LEFT JOIN Product_UPC AS UPC ON UPC.ProductID = p.id LEFT JOIN Brands AS b ON p.brand = b.id WHERE (p.id = '4959678') – LeeTee Nov 18 '11 at 13:13

SQL Server SELECT composite table

1 Answers1