I have two select queries to use in my project for getting data and displaying it in my web page. I want to join them so that I need to access data only once. But I don't know how to join them as one of the query contains group function. Could you help me? Thanks in Advance. Following are my queries,
First query is,
SELECT DISTINCT
a.user_id, a.employee_code, a.employee_type, a.employee_email, a.employee_name, a.home_country, a.user_name, a.user_password,
a.isPeoplesoftData, bb.Code AS company, c.location AS location_code, c.desc_loc AS location_desc, c.currency_cd, c.business_title,
c.deptid AS department_code, c.desc_dept AS department_name, (CASE WHEN (c.reports_to = '') THEN c.report_dotted_line_id ELSE c.reports_to END)
AS reports_to, (CASE WHEN (c.reports_to = '') THEN c.report_dotted_line_name ELSE c.reports_to_name END) AS reports_to_name,
c.desc_comp AS company_name, c.grade, CASE WHEN a.weekendType <> 0 THEN a.weekendType ELSE d .weekend_type END AS weekend_type,
e.country AS nationality, f.hire_dt, d.location_id, a.isActive, c.jobcode, c.desc_job, c.country, c.empl_status, dbo.FormatDate(h.birthdate, 'MM/dd/yyy')
AS birthdate, c.monthly_rt, c.annual_rt, i.AccountNo, d.hr
FROM dbo.users AS a LEFT OUTER JOIN
dbo.employees AS b ON a.employee_code = b.employee_code LEFT OUTER JOIN
dbo.employees_jobs AS c ON a.employee_code = c.employee_code AND c.id =
(SELECT TOP (1) id
FROM dbo.employees_jobs
WHERE (employee_code = b.employee_code) AND (effdt <= GETDATE()) AND (isDeleted = 0)
ORDER BY effdt DESC, id DESC) LEFT OUTER JOIN
dbo.employees_nationality AS e ON e.employee_code = a.employee_code LEFT OUTER JOIN
dbo.locations AS d ON c.location = d.location_name LEFT OUTER JOIN
dbo.employees_employment AS f ON f.employee_code = a.employee_code INNER JOIN
dbo.arabia_employee_entity AS aa ON a.user_id = aa.user_id INNER JOIN
dbo.Entity AS bb ON aa.entity_id = bb.EntityID LEFT OUTER JOIN
dbo.employees_person AS h ON a.employee_code = h.employee_code INNER JOIN
dbo.employee_bank_account AS i ON a.user_id = i.userID
WHERE (a.isActive = 1) AND (c.empl_status = 'A' OR
c.empl_status = 'L' OR
c.empl_status = 'P')
Second query is,
select currency_cd, SUM(comprate) as salary from employees_compensation where employee_code = @emp_code group by currency_cd
Note: The second query is actually a procedure. On joining I want the employee_code of the second query to be equal to the employee_code of employees table in the first query.