writing an query using outer join, confused by conditions needed or excluded

Question

EMPLOYEE (fmane, minit, lname, ssn, birthdate, address, sex, salary, superssn, dno)     KEY: ssn
DEPARTMENT (dname, dnumber, mgrssn, mgrstartdate)               KEY: dnumber.
PROJECT  (pname, pnumber, plocation, dnum)                          KEY: pnumber.
WORKS_ON (essn, pno, hours)                                         KEY: (essn, pno)
DEPENDENT  (essn, dependent-name, sex, bdate, relationship)             KEY: (essn, dependent-name)

I want to find the last name and ssn of all employees who worked more than 3 projects, but I want use outer join for this query only.

Select e.lname, e.ssn
from employee e
join e.ssn in (
select w.essn
from works_on w, project p
outer join w.pno = p.pnumber
group by w.essn
having count (*) >= 3
)

1) My outer join seems kind off or did I do need another join after the second join?

2)the one thing that confuse me is if just want the lname and not essn, do I change the e.ssn in and select w.essn inside the nested query to ssn and essn?

JohnLBevan · Answer 1 · 2015-12-08T22:26:06.837

1

--get the last name and ssn
Select e.lname, e.ssn
--of every employee
from employee e
--where the employee worked on 
inner join works_on w
on w.essn = e.ssn
group by e.lname, e.ssn
--at least 3 different projects
having count(distinct w.pno) >= 3

edited Dec 08 '15 at 22:26

answered Dec 08 '15 at 22:25

JohnLBevan

22,735
13
96
178

Thanks, but your answer is inner join? can you please write in outer join and I will accept it – liam chang Dec 08 '15 at 22:25
There's no reason to use an outer join here; you only want employees who've worked on at least 3 projects, therefore there must be results from the `works_on` table (an outer join would include employees who'd worked on no projects; then our having statement would exclude them anyway). If you wanted you could simply replace `inner join` with `left outer join` and you'd get the same results; but there's no benefit to doing that. – JohnLBevan Dec 08 '15 at 22:28

WBratz · Accepted Answer · 2015-12-11T16:51:31.547

FULL JOIN and FULL OUTER JOIN in SQL are interchangeable. Also you have to be aware of what goes in the WHERE clause and where your joins go. I think of it this way. You JOIN these tables ON this condition and that's how you write it. NOT ON this condition JOIN these tables which is how you have it written.

If you are looking for something from a single column list from a single column list IN another query, you want to use a subquery in the Where cause. SubQueries in the from clause are more for queries that make a table I use subqueries in the FROM clause a lot for example when I'm doing multiple calculations from columns on a single table. For example imagine I have a table that has numbers of men and women separated by country and region and I want to get total populations I would use a query in the from clause like this

SELECT Country, SUM(MEN + WOMEN) [Total Population]
FROM(
  SELECT Country, SUM(Men) Men, Sum(Women) Women
  FROM dbo.countries
  WHERE country = 'US'
  GROUP BY Country) tbl
GROUP BY Country

Here is your query with the correct syntax and restructured a little hope it helps.

SELECT lname, ssn
FROM employee
WHERE ssn in (
    select w.essn
    from works_on w
    FULL OUTER JOIN project p
        ON w.pno = p.pnumber
    group by w.essn
    having count (*) >= 3
)

writing an query using outer join, confused by conditions needed or excluded

2 Answers2