How to join two tables in Sql Server?

Answer:

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).