To give inner join and Left outer Join

[pol]

In have three table

1. tb_Employee ( id, emp_name,departmentid)
2. tb_Department ( id,dept_name)
3. tb_salary(id,empid,deptid,sal_month)

I need to give the following sql

Select a.id,a.name,b.dept_name,c.sal_month
from tb_Employee a, tb_Department b, tb_salary c
where a.departmentid = b.departmentid ( Inner join )
and a.id (+) = c.empid ( Outer Join )

How I can give the same sql in Ms Access. Please let me know.

With Thanks and regards

Polachan

 

[Jack Cannon]

Polachan

Assuming that emp_name and name are equivalent then the following should work.

SELECT tb_Employee.id, tb_Employee.emp_name, tb_Department.dept_name,
tb_salary.sal_month
FROM (tb_Employee INNER JOIN tb_Department ON tb_Employee.id =
tb_Department.id) LEFT JOIN tb_salary ON tb_Employee.id = tb_salary.empid;

Jack Cannon

 

[Tom Wickerath]

Hi Polachan,

Select a.id,a.name,b.dept_name,c.sal_month....

I don't see a field named "name" in the table that you aliased as "a", but
that's a good thing, since the word "name" is considered a reserved word:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

Try this SQL statement. Note that Access (JET) requires that one use the
"AS" keyword when aliasing a table:

SELECT A.ID, A.emp_name, B.dept_name, C.sal_month
FROM (tb_Employee AS A
INNER JOIN tb_Department AS B ON A.ID = B.ID)
LEFT JOIN tb_salary AS C ON A.ID = C.deptid;


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Jack Cannon]

Ooops!

Small error in my earlier posting.
This will work better.

SELECT tb_Employee.id, tb_Employee.emp_name, tb_Department.dept_name,
tb_salary.sal_month
FROM (tb_Employee LEFT JOIN tb_salary ON tb_Employee.id = tb_salary.empid)
INNER JOIN tb_Department ON tb_Employee.departmentid = tb_Department.id;

Jack Cannon