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 groupby 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.
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
JOIN
(select currency_cd,employee_code , SUM(comprate) as salary from employees_compensation where employee_code = @emp_code group by currency_cd,employee_code) as tmp
ON tmp.employee_code = a.employee_code
WHERE (a.isActive = 1) AND (c.empl_status = 'A' OR
c.empl_status = 'L' OR
c.empl_status = 'P')
Talha Ashfaque -- Code ShodePlease Mark as Answer if this post helps you!
Marked as answer by javedhakim on Jun 18, 2012 08:28 AM
javedhakim
Member
88 Points
187 Posts
Joining Two Queries
Jun 18, 2012 04:28 AM|LINK
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,
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.
tjaank
Contributor
6688 Points
1204 Posts
Re: Joining Two Queries
Jun 18, 2012 05:26 AM|LINK
try this:
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 JOIN (select currency_cd,employee_code , SUM(comprate) as salary from employees_compensation where employee_code = @emp_code group by currency_cd,employee_code) as tmp ON tmp.employee_code = a.employee_code WHERE (a.isActive = 1) AND (c.empl_status = 'A' OR c.empl_status = 'L' OR c.empl_status = 'P')Please Mark as Answer if this post helps you!