sql join on a subquery
SELECT * FROM ( SELECT ... FROM ... ) q1 INNER JOIN ( SELECT ... FROM ... ) q2 ON q1.colname = q2.colname
Source: stackoverflow.com
sql join on a subquery
(select coalesce((select name from object_value where object_id = addtl_type.id and object_type_id = @object_type_des_UDF and language_id = @language_id),addtl_type.name) from addtl_type where id = (select n_value from pr_addtl_data where pr_id = PR.ID and data_field_id = (select id from data_fields where name = 'Category'))) as 'Category',