Click here to Skip to main content
15,910,358 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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,
SQL
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,

SQL
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.
Posted

1 solution

I got headache by seeing this query...... Use Instant SQL Fomatter[^] to for long sql queries.

Use the below Example to solve your problem.

SQL
Select EmployeeID, EmployeeName, EmployeeSSN, Designation from Employee
union
Select 0 as EmployeeID, ManagerName as EmployeeName, 0 as EmployeeSSN , 'Manager' as Designation From Management 


Hope this helps....

Regards
Sebastian
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900