Help with JOIN

[zeep25]

I have three tables like this:

 

subjects

+-----------+-----------+
| SubjectID |   Name    |
+-----------+-----------+
|       50  |   English |
|       51  |   Math    |
|       52  |   History |
|       53  |   Arts    |
|       54  |   Music   |
+-----------+-----------+

 

grades

+---------+--------------+
| GradeID |     Name     |
+---------+--------------+
|     10  |   Tenth      |
|     11  |   Eleventh   |
|     12  |   Twelfth    |
|     13  |   College    |
|     14  |   HomeSchool |
+---------+--------------+

 

 

products

+-----------+-----------+----------+--------------+
| ProductID | SubjectID | GradeID  |    Name      |
+-----------+-----------+----------+--------------+
|     84512 |    50,53  | 10,11,12 |   Product 1  |
|     84513 | 54,50,51  |    11,12 |   Product 2  |
|     84514 |    53,51  | 14,12,13 |   Product 3  |
+-----------+-----------+----------+--------------+

 

As you can see, each product has multiple subjects and grades associated with it. The way I am getting the required info for each product. I am doing three queries like this:

 

SELECT 
* 
FROM 
products 
WHERE 
ProductID='84512'

then the use the result from that to do this:

SELECT 
* 
FROM 	
subjects 
WHERE 
FIND_IN_SET(SubjectID, '50,53')

and then finally get the grade levels for the product 84512

SELECT 
* 
FROM 	
grades 
WHERE 
FIND_IN_SET(GradeID, '10,11,12')

 

Now I wanted to know can i use JOIN or WHERE to get all the data (the two different subject names, and the three different grade levels) in one query?

 

Info:

Processor: 4 x Xeon 3 Ghz

OS: RedHat Linux

Memory : 2 GB

Mysql: 4.1.22-standard

[MadTechie]

I guess something like this

 

Select products.Name, grades.Name, subjects.Name 
Left JOIN grades ON grades.GradeID IN (products.GradeID)
Left JOIN subjects ON subjects.SubjectID IN (products.SubjectID)
WHERE products.ProductID = 84512;

 

 

**UNTESTED

[bubblegum.anarchy]

This is a fun query to create:

SELECT
ProductID
, group_concat(DISTINCT subjects.Name SEPARATOR '\n') AS subject_names
, group_concat(DISTINCT grades.Name SEPARATOR '\n') AS grade_names
, products.Name
FROM products
INNER JOIN subjects ON find_in_set(subjects.SubjectID, products.SubjectID)
INNER JOIN grades ON find_in_set(grades.GradeID, products.GradeID)
WHERE ProductID = 84512
GROUP BY ProductID