Only one expression can be specified in the select list when the subquery is not introduced with EXI-CodePudding

Select e.first_name ' ' e.last_name As FullName, j.job_title, 
(
    Select Max(start_date) As StartDate, Max(end_date) As EndDate
    From job_history as jh1
    Where jh1.employee_id = e.employee_id
    Group by employee_id
)    
From employees e, jobs j
Where e.job_id = j.job_id
And e.commission_pct = 0;

I would like to return the last job starting and ending dates of some employees. who don't get commissions and name (From employees table) and job_title (From jobs table). If a sub query is used as a column can't we have more than single column in it?

CodePudding user response：

Unfortunately, this is impossible. you can easily solve this by joining or you can write a function for max, min and find it for any id and return the result by typing SQL in it.

it should work that way:

Select e.first_name ' ' e.last_name As FullName, T.StartDate , T.EndDate
From employees e INNER JOIN  jobs j
ON e.job_id = j.job_id and e.commission_pct = 0 
INNER JOIN (   
Select Max(start_date) As StartDate, Max(end_date) As EndDate
    From job_history as jh1
Group by jh1.employee_id 
) AS T 
ON T.employee_id = e.employee_id

CodePudding user response：

You could use a common table expression:

;WITH CTE
AS
(
    SELECT employee_id, 
           Max(start_date) As StartDate, 
           Max(end_date) As EndDate
    FROM job_history
    GROUP by employee_id
)
SELECT e.first_name ' ' e.last_name As FullName, 
       j.job_title, 
       C.StartDate , C.EndDate
FROM employees e 
INNER JOIN  jobs j
    ON e.job_id = j.job_id 
INNER JOIN CTE C
    ON C.employee_id = e.employee_Id
WHERE e.commission_pct = 0

CodePudding user response：

A CROSS APPLY may be what you need:

Select e.first_name ' ' e.last_name As FullName, T.StartDate , T.EndDate
From employees e
Join jobs j On e.job_id = j.job_id 
Cross Applu (   
    Select Max(start_date) As StartDate, Max(end_date) As EndDate
    From job_history as jh1
    Where jh1.employee_id = e.employee_id
) As T
Where e.commission_pct = 0

I also recommend moving the commission_pct test into the WHERE as shown above, since it is really not functioning as a join condition.

The CTE group-by, subquery group-by, and cross-apply answers given so far all accomplish the same thing. Which is used is partly a matter of style, but they may also have performance differences depending on what portion of the data (% of employees) are being selected. If the % is high, the CTE and subqueries may perform better by preprocessing all job history data at once. If the percentage is small, there may be an advantage with cross apply only accessing that data needed for teh result.