CODEWITHSUNDEEP
sqlsql-server
San
San

Reputation: 19

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. (I am Using sub query as column expression.)

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?

Upvotes: 1

Views: 252

Answers (3)

T N
T N

Reputation: 10120

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 Apply (
    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 (almost*) 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 the result.

(* As noted by Charlieface in the comments below, there is a difference in behavior if an employee has no matching job history. The above outer-apply will always match, and may yield null StartDate and EndDate values. The CTE and subqueries -based answers perform an inner join which may exclude such an employee. The latter answers c an be easily changed to left-joins. You will need to decide on the preferred behavior.)

Upvotes: 2

Steve Ford
Steve Ford

Reputation: 7763

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

Upvotes: 0

Utkir Khujaev
Utkir Khujaev

Reputation: 93

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

Upvotes: 0

Related Questions