Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Posts
    14

    Question Unanswered: 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
    Sam

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, 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.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by SamKlinsmann
    Is there a way to DISPLAY this result in ONE row without changing the tables’ structure?
    Like -> Smith | Hat | Coat | Shoes
    there sure is, but only in mysql version 4.1+, and sybase ase
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by r937
    there sure is, but only in mysql version 4.1+, and sybase ase
    ... or Oracle 9i like this.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by andrewst
    ... or Oracle 9i like this.
    WTF OMG LOL

    that's horrible!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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).

  7. #7
    Join Date
    Dec 2003
    Posts
    14
    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"?

    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

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's called "denormalization"

    in mysql it's the GROUP_CONCAT function (see 12.9.1 GROUP BY (Aggregate) Functions)

    using your tables A,B,C above, you would write:
    Code:
    select AName
         , group_concat(BName separator ' | ') as BNames
      from A
    inner join C ...
    group by AName
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2003
    Posts
    14

    Thumbs up

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •