I am trying to do a join subquery to return just employee names that earn less than 46000 and I can get it to work by also returning the employee id but not without it.
This is how Im doing it.
Select e.eid, e.ename
From employee_table e
Inner Join (
Select salary, eid
from salary
Where salary > 46000
) as s
On e.eid = s.eid;
So the salary isn't stored in the employee table, but in a separate salary table. The salary table contains the employee ID. This makes this a 1:n relation, i.e. one employee can have more than one salary.
I don't know your tables, so I don't know the reason for this. Maybe an employee can have many jobs, or there are part salaries like a base salary and additional salaries, or there is a date range stored with the salary to indicate when it is/was valid. I don't know.
Let's say, we can simply add an employee's salaries to get the total. Then we select the employee names from the employee table where we find a salary less than 46000 in the salary table.
select ename
from employee
where eid in
(
select eid
from salary
group by eid
having sum(salary) < 46000
);