Subqueries

[Guest]

Dear Experts,

I would like to run a select statement with a select statement. For example,
I am having a table named "employee". I am selecting information of the
employee as well as the supervisor's and HOD's information. the supervisor's
and HOD's information are also in the same table. Therefore I came out with
my sql statement as follow:

SELECT employee.employeenum, employee.name, employee.supervisor AS supvno,
(select [name] from employee where employeenum=supervisor) AS Expr1
FROM Department INNER JOIN employee ON Department.Dep = employee.department;

However, the result displayed on the expr1 is all the same value although
the supervisor code are different. Please advise. Many thanks in advance.

 

[Allen Browne]

Alias the table in the subquery:

SELECT employee.employeenum, employee.name, employee.supervisor AS supvno,
(select Super.[name] from employee AS Super where
Super.employeenum=employee.supervisor) AS Expr1
FROM Department INNER JOIN employee ON Department.Dep = employee.department;

BTW, you could also do that as a query with ordinary joins.
Advantages:
- writable;
- easier to get multiple fields from both tables;
- more efficient and stable (esp. if used as source for another query.)

1. Create the basic query into the employee table.

2. Add the employee table to the query design window a 2nd time.
Access aliases it as employee_1. Open the properties box (View menu), and
change the Alias propery to (say) Super.

3. Drag employee.supervisor onto Super.employeenum.
Access shows the line joining the 2 tables.
Double-click that line.
Access offers a dialog.
Choose the option that says:
All results from employee, and any matches from Super.

4. Repeat for HOD if desired.