SQL : get rows from two tables relatively
Here is the query to order it the way the question has it

SELECT c.cat_name,p.prod_name
FROM products p INNER JOIN category c
ON p.cat_id = c.cat_id
ORDER BY prod_name;


Here is that query formatted

SELECT CONCAT('Category : ',c.cat_name,', Product ; ',p.prod_name) DisplayLine
FROM products p INNER JOIN category c
ON p.cat_id = c.cat_id
ORDER BY prod_name;


If you want to it order by category, then

SELECT c.cat_name,p.prod_name
FROM products p INNER JOIN category c
ON p.cat_id = c.cat_id
ORDER BY cat_name,prod_name;


Give it a Try !!!