How to join two tables in Sql Server?

You can write following sql statement 

select category.*, categoryparent.categoryparent from category, categoryparent where category.categoryparentid = categoryparent.autoid 

I am assuming here that category.categoryparentid (foreign key) is the value of categoryparent.autoid (primary key).