I haven't done much SQL for a few years so I wonder if somebody could give me a few pointers on something. Say I have the following to tables:

Product
ProductID (PK)
Name

Message
MessageID (PK)
MessageType
ProductID (FK)

Product's may be related to 0 or more messages.

What I want is a select statement that will retrieve the product ID, name and then any messages all in one row. So for example:



ID Name MessageType
ABC001 TestA MessageA
ABC002 TestB MessageB, MessageC
ABC003 TestC MessageA, MessageC

Is that possible?

The closest I can come up with is this select statement:


select concat(Product.ProductID," ",Name," ",MessageType) from Product,Message
where Product.ProductID=Message.ProductID;

This is basically a join from two tables (Product and Message) and would produce something like this:




ID Name MessageType
ABC001 TestA MessageA
ABC002 TestB MessageB
ABC002 TestB MessageC
ABC003 TestC MessageA
ABC003 TestC MessageC


You could return the result of this select statement into an array and go through a loop where so long as the ID is the same as the previous to only print the MessageType. Once the ID changes then you can start a new line where you begin the printing with the ID, Name, and MessageType.

Hope that helps.

Casey

Cheers Casey,

That's also as far as I got. I'll just have to keep exporting it into Excel and filtering it then :) Thanks for the help.

No no no... You can't let me get away that easily :)

I think I figured it out... this was driving me nuts. Try this:



mysql> SELECT Product.ProductID,Name, GROUP_CONCAT(DISTINCT
MessageType separator ',') AS 'Message Type' FROM Product,Message
WHERE Product.ProductID=Message.ProductID GROUP BY Product.ProductID;
+-----------+-------+-------------------+
| ProductID | Name | Message Type |
+-----------+-------+-------------------+
| 1 | TestA | MessageA |
| 2 | TestB | MessageB,MessageC |
| 3 | TestC | MessageA,MessageC |
+-----------+-------+-------------------+
3 rows in set (0.00 sec)

mysql>


I was beating my head against this as it should have worked. In the end it turned out that I needed a newer version of MySQL since the key was that the GROUP_CONCAT wasn't available until later releases of MySQL. It didn't work in MySQL 4.0 for me but did in 4.1.

Good luck.

Casey

Hmm, That Group_Concat function is neat, but is LT working in MY SQL or what? I do have VBA code for Access that will do this if that's the platform you are working on. Let me know.

Good question Scott. I figured I'd make an assumption and go from there to narrow the problem down.

I do believe that the GROUP_CONCAT() function is MySQL specific but from what I understand Oracle has a similar function called LIST() but I don't know how to use it.

I'm thinking/hoping now that we can figure out how to do it in MySQL we can tailor it to other SQLs.

Casey

I don't think Access SQL has any analogous function. This has to be done by looping through the recordset and concatenating the values.

I'm using SQL Server but from a quick search I did come across something on MSDN saying the function was there. I just haven't had a chance to try it yet :)