Welcome to WebmasterWorld Guest from 54.158.237.30

Forum Moderators: open

Message Too Old, No Replies

Join ORDER BY query

join ORDER BY query

     
10:39 pm on May 10, 2010 (gmt 0)

New User

5+ Year Member

joined:June 29, 2008
posts: 18
votes: 0


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.
10:51 pm on May 10, 2010 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5613
votes: 33


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
11:22 pm on May 10, 2010 (gmt 0)

New User

5+ Year Member

joined:June 29, 2008
posts:18
votes: 0


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

Thanks,

Ofie!