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 trialEric Ewers
13,976 PointsSQL 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
Jason Anello
Courses Plus Student 94,610 PointsHi 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