To give inner join and Left outer Join

To give inner join and Left outer Join

Post by cG9s » Thu, 26 Feb 2009 15:54:04


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
 
 
 

To give inner join and Left outer Join

Post by SmFjayBDYW » Thu, 26 Feb 2009 16:29:00

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

 
 
 

To give inner join and Left outer Join

Post by VG9tIFdpY2 » Thu, 26 Feb 2009 16:32:03

Hi Polachan,


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://www.yqcomputer.com/

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.yqcomputer.com/
http://www.yqcomputer.com/
__________________________________________
 
 
 

To give inner join and Left outer Join

Post by SmFjayBDYW » Thu, 26 Feb 2009 17:05:00

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