Catenating join result into one field

bubblenut

I'm not sure if this can be done and I'm not sure where to look in the manual (MySQL) to find out so I thought I'd see if anyone else knows.

I have a rather large and complex query which pulls out a whole host of product data, of which one field is "genre". The powers that be have just decided that all dvds should be able to have multiple genres to better specify what they are, fair enough. This can be easily done with the current database schema. However, we don't really want to be pulling off a whole extra row in the resultset just for an extra genre on a product and then checking if the product_code of this row is the same as the previous row and if it catenating the genres. At the moment our main product retrieval query pulls out a product per row and we'd like to keep it that way. Soooo, to what I had in mind, is there a way that we can have the join accross and pull out the multiple genres but cat them together into one field? Prehapse a comma-seperated list?

Any ideas?

Thanks
Bubble

drawmack

Yeah you can do that, but if you do you've just denormalized your database and haven't really saved any coding. Once you really think about it a change like this, no matter how it's done, is going to require rewritting your retrieval, adding and editing code anyway. Your best bet is to create a link table to link the dvds to the genres. Then just do a little piece of code that will populate the dvds to genres table from the current dvds table.

Now once that's done redo edit, delete, add and retrieve. Doing it this way also gives you the added benefit of being able to keep using your current code while you write the new code even after the database mods are made.

bubblenut

No, no, no, no, you misunderstand me. Keeping the database scheme fully normalized but just playing around with the select so that it apears that the genres are all coming out together in on field, just so that we don't get multiple rows per product in the result set. This would just be for the product_retrieval query, in other instances we would want to pull out the genres each in their own row.