SQL Server Combining select queries without same count of rows

ttcibm8c 于 2023-05-16 发布在其他

关注(0)|答案(2)|浏览(144)

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:

SELECT 
    dbo.Employees.Name,
    dbo.Employees.Salary
FROM 
    dbo.Employees

Second select query:

SELECT 
    dbo.Employees.Name AS Employee,
    dbo.Employees.Salary AS Salary,
    dbo.Salary.Bonus AS Bonus,
    dbo.Employees.Salary + dbo.Salary.Bonus AS BonusSalary
FROM 
    dbo.Employees 
INNER JOIN 
    dbo.Salary ON dbo.Employees.Id = dbo.Salary.Employee 
WHERE 
    dbo.Salary.Year = 2023
    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 tried to use UNION and EXCEPT But I get above error

sql-server

来源：https://stackoverflow.com/questions/76243306/combining-select-queries-without-same-count-of-rows

2条答案

按热度按时间

e0bqpujr1#

SELECT 
    dbo.Employees.Name AS Employee,
    dbo.Salary.forPurchase AS Purchases,
    dbo.Salary.forProduction AS Productions,
    dbo.Salary.forSale AS Sales,
    dbo.Employees.Salary AS Salary,
    dbo.Salary.Bonus AS Bonus,
    dbo.Employees.Salary + dbo.Salary.Bonus AS BonusSalary,
    COALESCE(dbo.Salary.Issued, 'salary was not paid') AS Issued
FROM 
    dbo.Employees
FULL OUTER JOIN
(
        dbo.Salary
    INNER JOIN 
        dbo.Months
            ON  Salary.Month = Months.Id
            AND Salary.Year  = 2023
            AND Salary.Month = 5       
)
    ON Employees.Id = Salary.Employee

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

SELECT 
    dbo.Employees.Name AS Employee,
    dbo.Salary.forPurchase AS Purchases,
    dbo.Salary.forProduction AS Productions,
    dbo.Salary.forSale AS Sales,
    dbo.Employees.Salary AS Salary,
    dbo.Salary.Bonus AS Bonus,
    dbo.Employees.Salary + dbo.Salary.Bonus AS BonusSalary,
    COALESCE(dbo.Salary.Issued, 'salary was not paid') AS Issued
FROM 
    dbo.Employees
FULL OUTER JOIN
    dbo.Salary
        ON  Salary.Employee = Employees.Id 
        AND Salary.Year     = 2023
        AND Salary.Month    = 5

赞(0）回复(0）举报 2023-05-16

3htmauhk2#

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

SELECT 
    dbo.Employees.Name AS Employee,
    dbo.Employees.Salary AS Salary,
    dbo.Salary.Bonus AS Bonus,
    dbo.Employees.Salary + dbo.Salary.Bonus AS BonusSalary
FROM 
    dbo.Employees 
left JOIN 
    dbo.Salary ON dbo.Employees.Id = dbo.Salary.Employee  and dbo.Salary.Year = 2023
    AND dbo.Salary.Month = 5

dbfiddle

赞(0）回复(0）举报 2023-05-16

我来回答

SQL Server Combining select queries without same count of rows

2条答案

相关问题

热门标签

最新问答