Combining select queries without same count of rows

huangapple
go评论96阅读模式
英文:

Combining select queries without same count of rows

问题

I try to combine two select query, but I get Error

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists

First select query:

  1. SELECT 
  2. 	dbo.Employees.Name,
  3. 	dbo.Employees.Salary
  4. FROM 
  5.     dbo.Employees

Second select query:

  1. SELECT 
  2. 	dbo.Employees.Name AS Employee,
  3. 	dbo.Employees.Salary AS Salary,
  4. 	dbo.Salary.Bonus AS Bonus,
  5. 	dbo.Employees.Salary + dbo.Salary.Bonus AS BonusSalary
  6. FROM 
  7.     dbo.Employees 
  8. INNER JOIN 
  9.     dbo.Salary ON dbo.Employees.Id = dbo.Salary.Employee 
  10. WHERE 
  11. 	dbo.Salary.Year = 2023
  12. 	AND dbo.Salary.Month = 5

The problem is that I need to combine these two queries. If there are not enough employees in the second query, I need to display them anyway.

I need to display absolutely all employees, regardless of the date.

英文:

I try to combine two select query, but I get Error

> All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists

First select query:

  1. SELECT 
  2. 	dbo.Employees.Name,
  3. 	dbo.Employees.Salary
  4. FROM 
  5.     dbo.Employees

Combining select queries without same count of rows

Second select query:

  1. SELECT 
  2. 	dbo.Employees.Name AS Employee,
  3. 	dbo.Employees.Salary AS Salary,
  4. 	dbo.Salary.Bonus AS Bonus,
  5. 	dbo.Employees.Salary + dbo.Salary.Bonus AS BonusSalary
  6. FROM 
  7.     dbo.Employees 
  8. INNER JOIN 
  9.     dbo.Salary ON dbo.Employees.Id = dbo.Salary.Employee 
  10. WHERE 
  11. 	dbo.Salary.Year = 2023
  12. 	AND dbo.Salary.Month = 5

Combining select queries without same count of rows

The problem is that I need to combine these two queries. If there are not enough employees in the second query, I need to display them anyway.

I need to display absolutely all employees, regardless of the date

I tried to use UNION and EXCEPT
But I get above error

答案1

得分: 2

  1. SELECT 
  2.     dbo.Employees.Name AS 员工,
  3.     dbo.Salary.forPurchase AS 采购,
  4.     dbo.Salary.forProduction AS 生产,
  5.     dbo.Salary.forSale AS 销售,
  6.     dbo.Employees.Salary AS 工资,
  7.     dbo.Salary.Bonus AS 奖金,
  8.     dbo.Employees.Salary + dbo.Salary.Bonus AS 奖金工资,
  9.     COALESCE(dbo.Salary.Issued, '工资未发放') AS 已发放
  10. FROM 
  11.     dbo.Employees
  12. FULL OUTER JOIN
  13.     dbo.Salary
  14.         ON  Salary.Employee = Employees.Id 
  15.         AND Salary.Year     = 2023
  16.         AND Salary.Month    = 5
英文: 
  1. SELECT 
  2.     dbo.Employees.Name AS Employee,
  3.     dbo.Salary.forPurchase AS Purchases,
  4.     dbo.Salary.forProduction AS Productions,
  5.     dbo.Salary.forSale AS Sales,
  6.     dbo.Employees.Salary AS Salary,
  7.     dbo.Salary.Bonus AS Bonus,
  8.     dbo.Employees.Salary + dbo.Salary.Bonus AS BonusSalary,
  9.     COALESCE(dbo.Salary.Issued, 'salary was not paid') AS Issued
  10. FROM 
  11.     dbo.Employees
  12. FULL OUTER JOIN
  13. (
  14.         dbo.Salary
  15.     INNER JOIN 
  16.         dbo.Months
  17.             ON  Salary.Month = Months.Id
  18.             AND Salary.Year  = 2023
  19.             AND Salary.Month = 5       
  20. )
  21.     ON Employees.Id = Salary.Employee

But, as you don't actually use the Months table, it can be simplified to...

  1. SELECT 
  2.     dbo.Employees.Name AS Employee,
  3.     dbo.Salary.forPurchase AS Purchases,
  4.     dbo.Salary.forProduction AS Productions,
  5.     dbo.Salary.forSale AS Sales,
  6.     dbo.Employees.Salary AS Salary,
  7.     dbo.Salary.Bonus AS Bonus,
  8.     dbo.Employees.Salary + dbo.Salary.Bonus AS BonusSalary,
  9.     COALESCE(dbo.Salary.Issued, 'salary was not paid') AS Issued
  10. FROM 
  11.     dbo.Employees
  12. FULL OUTER JOIN
  13.     dbo.Salary
  14.         ON  Salary.Employee = Employees.Id 
  15.         AND Salary.Year     = 2023
  16.         AND Salary.Month    = 5

答案2

得分: 1

I realized that you want the whole employee to calculate her salary if it is in the salary table

  1. SELECT 
  2.     dbo.Employees.Name AS 员工,
  3.     dbo.Employees.Salary AS 工资,
  4.     dbo.Salary.Bonus AS 奖金,
  5.     dbo.Employees.Salary + dbo.Salary.Bonus AS 奖金工资
  6. FROM 
  7.     dbo.Employees 
  8. LEFT JOIN 
  9.     dbo.Salary ON dbo.Employees.Id = dbo.Salary.Employee  and dbo.Salary.Year = 2023
  10.     AND dbo.Salary.Month = 5

dbfiddle

英文:

I realized that you want the whole employee to calculate her salary if it is in the salary table

  2. SELECT 
  3.     dbo.Employees.Name AS Employee,
  4.     dbo.Employees.Salary AS Salary,
  5.     dbo.Salary.Bonus AS Bonus,
  6.     dbo.Employees.Salary + dbo.Salary.Bonus AS BonusSalary
  7. FROM 
  8.     dbo.Employees 
  9. left JOIN 
  10.     dbo.Salary ON dbo.Employees.Id = dbo.Salary.Employee  and dbo.Salary.Year = 2023
  11.     AND dbo.Salary.Month = 5

dbfiddle

huangapple
  • 本文由 发表于 2023年5月13日 22:46:58
  • 转载请务必保留本文链接：https://go.coder-hub.com/76243306.html
  • sql
  • sql-server
  • t-sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定