How to GROUP one record from multiple tables

I have two tables lesson and subjects. It is a one to many ratio as in each lesson can be categorized under 1 or more subjects resulting in something like "Name" subject: math, science, social studies

This would return 3 records:
Name, math
Name, science
name, social studies

This results in my looping through and combining and messes up my search count
How can I get one record with all three instead? So my search count is accurate and I don't need extra PHP code to check if name is the same and gather the extra subject?

I've tried basic

SELECT * FROM lesson INNER JOIN subjects ON subject.id = lesson.subject

But this results in 3 entries

EDIT
My query is more complicated than I lead on. I have a middle table keeping tracking of the two tables above and their relation
This query is for searching. Here's what I have.

SELECT name, subject        
FROM lesson As l
INNER JOIN lesson_sub As ls
  ON ls.lesson_id = l.id
INNER JOIN subjects As s
  ON s.id = ls.subject_id
WHERE CONCAT(l.name, s.subject) LIKE '%KEYWORD%' AND s.id = SUBJECT_ID

Solution

You can use the GROUP_CONCAT() function with your JOIN query:

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

GROUP_CONCAT(expr)

This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values. The full syntax is as follows:

GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] [SEPARATOR str_val])

mysql> SELECT student_name, -> GROUP_CONCAT(test_score) -> FROM student -> GROUP BY student_name;

Or:

mysql> SELECT student_name, -> GROUP_CONCAT(DISTINCT test_score -> ORDER BY test_score DESC SEPARATOR ' ') -> FROM student -> GROUP BY student_name;

So:

SELECT lesson.name, GROUP_CONCAT(subjects.name SEPARATOR ', ')
    FROM lesson JOIN subjects ON (subject.id = lesson.subject)
    GROUP BY lesson.name;

TEST

CREATE TABLE lesson ( name varchar (20), subject integer );
CREATE TABLE subjects ( id integer, name varchar(20) );

INSERT INTO subjects VALUES ( 1, 'Math' ), ( 2, 'Physics' ), ( 3, 'Chemistry' );

INSERT INTO lesson VALUES ( 'Lesson A', 1 );
INSERT INTO lesson VALUES ( 'Lesson A', 2 );
INSERT INTO lesson VALUES ( 'Lesson A', 3 );
INSERT INTO lesson VALUES ( 'Lesson B', 2 );
INSERT INTO lesson VALUES ( 'Lesson B', 3 );
INSERT INTO lesson VALUES ( 'Lesson C', 1 );

SELECT lesson.name, GROUP_CONCAT(subjects.name SEPARATOR ', ')
    FROM lesson JOIN subjects ON (subjects.id = lesson.subject)
    GROUP BY lesson.name;

+----------+--------------------------------------------+
| name     | GROUP_CONCAT(subjects.name SEPARATOR ', ') |
+----------+--------------------------------------------+
| Lesson A | Math, Chemistry, Physics                   |
| Lesson B | Chemistry, Physics                         |
| Lesson C | Math                                       |
+----------+--------------------------------------------+
3 rows in set (0.00 sec)

MORE COMPLICATED TEST (with intermediate table)

CREATE TABLE lesson ( id integer, name varchar (20) );
CREATE TABLE subjects ( id integer, name varchar(20) );
CREATE TABLE lesson_sub ( lesson_id integer, subject_id integer );

INSERT INTO subjects VALUES ( 1, 'Math' ), ( 2, 'Physics' ), ( 3, 'Chemistry' );
INSERT INTO lesson VALUES ( 1, 'Lesson A' ), ( 2, 'Lesson B' ), ( 3, 'Lesson C' );

INSERT INTO lesson_sub VALUES (1,1), (1,2),(1,3),(2,2),(2,3),(3,1);

SELECT lesson.name, GROUP_CONCAT(subjects.name SEPARATOR ', ') AS subjects
    FROM lesson_sub JOIN lesson ON ( lesson.id = lesson_sub.lesson_id )
                    JOIN subjects ON (subjects.id = lesson_sub.subject_id)
    WHERE CONCAT(lesson.name, subjects.name) LIKE '%Chem%'
    GROUP BY lesson.name;

SELECT name, subjects FROM (
    SELECT lesson.name, GROUP_CONCAT(subjects.name SEPARATOR ', ') AS subjects
    FROM lesson_sub JOIN lesson ON ( lesson.id = lesson_sub.lesson_id )
                    JOIN subjects ON (subjects.id = lesson_sub.subject_id)
    GROUP BY lesson.name ) AS lesson_clear
    WHERE CONCAT(name, subjects) LIKE '%Chem%';

+----------+--------------------------------------------+
| name     | GROUP_CONCAT(subjects.name SEPARATOR ', ') |
+----------+--------------------------------------------+
| Lesson A | Chemistry                                  |
| Lesson B | Chemistry                                  |
+----------+--------------------------------------------+
2 rows in set (0.00 sec)

+----------+--------------------------+
| name     | subjects                 |
+----------+--------------------------+
| Lesson A | Physics, Math, Chemistry |
| Lesson B | Physics, Chemistry       |
+----------+--------------------------+
2 rows in set (0.00 sec)