Join ORDER BY query

[ofie2]

Table 1 [CD_table]


cd_id | cd_name | cd_subcategory | cd_maincategory
--------------------------------------------------
1 | Beatles | 1 | 1
2 | Mozart | 4 | 1
3 | ZZ Top | 2 | 1
4 | Heart | 3 | 1
5 | The Script | 2 | 2



Table 2 [subcategory_table]

subcategory_id | subcategory_name | category_desc
---------------------------------------------------------
1 | English Rock | English Classic Rock & Roll
2 | American Rock | Classic American Rock & Roll
3 | Chick Music | Songs that girls like
4 | Classical | Classical Music


My goal is to list all items in CD_table based WHERE cd_maincategory=1 and ORDER BY subcategory_name ASC from table subcategory_table

I've tried:

$query = sprintf("SELECT * FROM CD_table, subcategory_table WHERE cd_maincategory='1' ORDER BY subcategory_table.subcategory_name ASC");
$results = mysql_query($query);

But I'm missing something basic.

[LifeinAsia]

This should work:
SELECT *
FROM CD_table INNER JOIN subcategory_table ON CD_table.cd_subcategory=subcategory_table.subcategory_id
WHERE CD_table.cd_maincategory=1
ORDER BY subcategory_table.subcategory_name

[ofie2]

Awesome, works like a charm. Sometimes I over think things and miss the easy ones.

Thanks,

Ofie!