Displaying the query result in one row

Hi all,

I have two M:M tables, A and B, which are connected by a 3rd link table C.
Each row of A might be connected to 0 to 10 row(s) of B.

A (ID, AName)
C (A.ID, B.ID)
B (ID, Bname)

And when I query AName = Smith, normally, it returns:

Smith | Hat
Smith | Coat
Smith | Shoes

My question:
Is there a way to DISPLAY this result in ONE row without changing the tables structure?
Like -> Smith | Hat | Coat | Shoes

Any comment and help would be appreciated.

Thanks
SamYes, but not using pure SQL. You will have to write application code to query the data order by ANAME and then keep concatenating the BNAME values together until the ANAME changes.|||Is there a way to DISPLAY this result in ONE row without changing the tables structure?
Like -> Smith | Hat | Coat | Shoesthere sure is, but only in mysql version 4.1+, and sybase ase|||there sure is, but only in mysql version 4.1+, and sybase ase
... or Oracle 9i like this (http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2196162600402).|||... or Oracle 9i like this (http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2196162600402).W TF OMG LOL

that's horrible!!

:cool:|||True, but at least Tom Kyte has done all the work for us. All we have to do is install his STRAGG function once, make it public, and from then on just use it, i.e.:

select deptno, stragg(ename)
from emp
group by deptno;

(But it would be neat if it came built-in to the DBMS I agree).|||Thanks a lot Tony and Rudy.

I thought there might be a SQL trick (!) to do that and I would put myself in disadvantage if I didn't ask you about that.

I will try this again when I start my application development. Any reference, link, or hint on this subject would be helpful for me. I am not even sure what such a process is called, "multi-column to one-row"? :D

Rudy, I was wondering if you could address the section of MySQL manual for doing that job in MySQL 4.1+ as you mentioned. (I am going to use MySQL 4.1.x and PHP)

Thanks again
Sam|||it's called "denormalization"

in mysql it's the GROUP_CONCAT function (see 12.9.1 GROUP BY (Aggregate) Functions (http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html))

using your tables A,B,C above, you would write:select AName
, group_concat(BName separator ' | ') as BNames
from A
inner join C ...
group by AName|||Yes! That was what I was looking for.

Thank you very much Rudy. You solved my problem again. Your solution works very well. Especially, with that separator '|' you recommended, the result is very readable.

You are not only very knowledgeable but also an awesome teacher. I must enroll in your "online course SQL for Database-Driven Web Sites". I am sure I will learn a lot from you.

Regards,
Sam