Can I reduce the lines of my MySQL output by concatenation?

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Asteras

    Can I reduce the lines of my MySQL output by concatenation?

    Hi All!!!

    Let us consider two tables in a MySQL database:

    1) C_T is a table of school classes and the teachers that teach in
    them:

    select * from C_T;

    +-------+---------+
    | Class | Teacher |
    +-------+---------+
    | A | John |
    | A | Robert |
    | B | Julian |
    | C | George |
    | C | John |
    +-------+---------+

    2) T_S is a table that shows the subject of each teacher.

    select * from T_S;

    +---------+-----------+
    | Teacher | Subject |
    +---------+-----------+
    | John | Maths |
    | Julian | Chemistry |
    | George | Physics |
    | Robert | Music |
    +---------+-----------+

    This is the ERD schema of the MySQL database:

    +-----------------+ +-------------+
    | C_T | | T_S |
    +-----------------+\ +-------------+
    | Class PK |-+---+| Teacher PK |
    | Teacher PK, FK |/ | Subject |
    +-----------------+ +-------------+

    As you can see, the tables have an "one to many" relationship.

    C_T has a composite primary key (Class and Teacher) and T_S's primary
    key is Teacher (which is exported as an identifying foreign key in C_T
    table).

    If I try:

    select C_T.Class, C_T.Teacher, T_S.Subject
    from C_T, T_S
    where C_T.Teacher=T_S .Teacher;

    +-------+---------+-----------+
    | Class | Teacher | Subject |
    +-------+---------+-----------+
    | A | John | Maths |
    | A | Robert | Music |
    | B | Julian | Chemistry |
    | C | George | Physics |
    | C | John | Maths |
    +-------+---------+-----------+

    I can see all the entries of C_T (attributes Class and Teacher)
    together with the relevent entries of T_S from attribute Subject.

    Is there a way I can get in a SINGLE line ALL the entries of a single
    primary key of the first table? In my example, can I get a 3-line
    output where each line would contain each Class, the names of the
    Teachers separated by a semicolon, and the Subjects taught in the
    Class, also separated by a semicolon? In other words, I want to get
    the following:

    +-------+-------------+---------------+
    | Class | Teacher | Subject |
    +-------+-------------+---------------+
    | A | John;Robert | Maths,Music |
    | B | Julian | Chemistry |
    | C | George;John | Physics;Maths |
    +-------+-------------+---------------+

    If there is a way, I would be indebted if you could show it to me.

    Regards,

    Asteras
  • Bill Karwin

    #2
    Re: Can I reduce the lines of my MySQL output by concatenation?

    Asteras wrote:[color=blue]
    > Is there a way I can get in a SINGLE line ALL the entries of a single
    > primary key of the first table?[/color]

    This is a very similar problem to another that just came across this
    newsgroup today, under the subject "is this possible with selects and
    joins?"

    My answer would be very similar: fetch the data in its "expanded" form,
    and then do the concatenation in application code.

    Regards,
    Bill K.

    Comment

    Working...