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:

Code:

---

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:

Code:

---

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:

Code:

---

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:

Code:

---

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 :)