simple sql problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

simple sql problem

Author

Topic

lenzzz
Starting Member

4 Posts

Posted - 2010-08-19 : 19:16:32

list the identity and name ( first and last name concatenated) of all employees based in a location with an out-postocde of "0x9"

employees(emp_id, emp_firstname, emp_lastname, emp_email, emp_phoneno, emp_hiredate, emp_jobid, emp_salary, dep_id*)

departments(dep_id, dep_name, loc_id*)

locations(loc_id, loc_street, loc_city, loc_postcode)

im struggling because to get to locations table it will mean i have to join 3 tables together? help! thnx :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-19 : 19:32:21

what have you tried ?

KH
[spoiler]Time is always against us[/spoiler]

lenzzz
Starting Member

4 Posts

Posted - 2010-08-19 : 19:36:49

well i havent really tried anything because i cant test it on my comp..

SELECT emp_id, emp_firstname, emp_lastname, loc_postcode
FROM employees JOIN
ON
WHERE

i dont know what type of join to use because there are 3 tables whereas im use to dealing with 2

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-19 : 19:45:51

you can use INNER JOIN. You will need to JOIN employee to departments table and then join to locations table

this is not the full code, just showing the syntax of the join. Try . .


SELECT emp_id, emp_firstname, emp_lastname, loc_postcode
FROM   employees 
      JOIN departments ON  <the join condition here>
WHERE <the where condition here>

KH
[spoiler]Time is always against us[/spoiler]

lenzzz
Starting Member

4 Posts

Posted - 2010-08-19 : 20:32:26

so would this work..

SELECT emp_id, emp_firstname + ' ' + emp_lastname, loc_postcode
FROM employees JOIN
JOIN DEPRTMENTS
WHERE Loc_postcode = 'ox9_*';

lenzzz
Starting Member

4 Posts

Posted - 2010-08-19 : 20:51:39

SELECT e.emp_id, e.emp_firstname + ' ' + e.emp_lastname, l.loc_postcode
FROM employees e JOIN locations l
ON e.emp_id = l.loc_id);
WHERE Loc_postcode = 'ox9_*';

???

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-19 : 21:48:06

quote:

Originally posted by lenzzz

so would this work..


SELECT emp_id, emp_firstname + ' ' + emp_lastname, loc_postcode
FROM employees JOIN 
JOIN departments ON employee.dept_id = departments.dep_id
WHERE Loc_postcode = 'ox9_*';

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources