Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Eric Ewers
Eric Ewers
13,976 Points

SQL SELECT - List rows with matching columns, but remove duplicate column

In my "agenda" database, I have two tables: "sessions" and "speakers".

In my "speakers" table, I have a column labeled "Sessions" that matches the column labeled "Title" in my "sessions" table.

For example:

speakers.Session = "This is my topic title" sessions.Title = "This is my topic title"

I would like to list out all of the sessions, then below each session, list out the speakers belonging to that session.

Here is what I have so far:

    // AGENDA
    function showAgenda($conference) {
        $query = mysqli_query($GLOBALS['con3'],"SELECT CONCAT(speakers.FirstName, ' ', speakers.LastName) SpeakerName, sessions.Title SessionTitle 
                                                FROM sessions 
                                                LEFT JOIN speakers ON sessions.Title = speakers.Session
                                                WHERE Conference LIKE '%$conference%'");
        while($records = mysqli_fetch_array($query)) {
            echo '<strong>'.$records['SessionTitle'].'</strong><br /><br />';
            echo $records['SpeakerName'].'<br /><br />';
        }
    }

The problem with this is that if there are more than two speakers matching a session, it will list that session twice.

Like so:

This is my topic title Speaker 1

This is my topic title Speaker 2

This is another topic title Speaker 3

What I want to happen is:

This is my topic title Speaker 1 Speaker 2

This is another topic title Speaker 3

1 Answer

Hi Eric,

Have you tried grouping by the session title? Then you could do a group_concat() on the speaker names.

Something like this:

SELECT sessions.Title AS SessionTitle,
       GROUP_CONCAT(
            CONCAT(speakers.FirstName, ' ', speakers.LastName)
            ORDER BY LastName ASC
            SEPARATOR ', ') AS SpeakerName 
       FROM sessions
       LEFT JOIN speakers ON sessions.Title = speakers.Session
       WHERE Conference LIKE '%$conference%'
       GROUP BY SessionTitle