Outer join query

[Gunnar Vøyenli]

Hi!
I have a problem with a query:
Two tables:
CREATE TABLE Emp (empno INT, depno INT)
CREATE TABLE Work (empno INT, depno INT, date DATETIME)

I want a list of all employees that belongs to a department (from Emp
table), together with ("union") all employeees WORKING on that department a
spescial day (An employee can have been borrowed from another department
which he does not belong)

Sample data
INSERT INTO Emp (empno, depno) VALUES (1,10)
INSERT INTO Emp (empno, depno) VALUES (2,10)
INSERT INTO Emp (empno, depno) VALUES (3,20)

INSERT INTO Work (empno, depno, date) VALUES (1,10,'2003-10-17')
INSERT INTO Work (empno, depno, date) VALUES (3,10,'2003-10-17')
INSERT INTO Work (empno, depno, date) VALUES (3,10,'2003-10-18')

Note that Employee 3 works on a department to which he does not belong (he
is borrowed to another department)

The following query
SELECT empno, depno, date FROM work WHERE depno = 10 AND date = '2003-10-17'
gives me this result set:

empno depno date
1 10 2003-10-17 00:00:00.000
3 10 2003-10-17 00:00:00.000

But I want employee 2 to appear in the result set as well, because he
belongs to department 10 (eaven thoug he is not working this particular day)

The result set should look like this
empno depno date
1 10 2003-10-01 00:00:00.000
2 10 NULL
3 10 2003-10-01 00:00:00.000

I have tried different approaches, but none of them is good.
Could someone please help me?
Thanks in advance

Regards,
Gunnar Vøyenli
EDB-konsulent as
NORWAY

[David Portas]

SELECT empno, depno,
CASE [date] WHEN '20031017' THEN [date] END AS [date]
FROM Work
WHERE depno = 10

Date is a reserved word and shouldn't be used as a column name (it's a
pretty meaningless name for a column anyway - Date of what?)

--
David Portas
------------
Please reply only to the newsgroup
--

[Gunnar Vøyenli]

Thanks for your reply, but am afraid this will not do.

I need data from BOTH the tables, not only from Work.
With your query, employee 2 will not be included in the result set, because
he belongs to the Employee table.

In other words: I want a list of all employees who belongs to depno 10,
TOGETHER with all employees which does not belong to depno 10, but work on
depno 10 this particular day.

We are talking about two categories of employees:
1) All the employees who belong to depno 10 (whether they work this day or
not)
2) Those employees who does NOT belong to depno 10, BUT is working at depno
10 this date.

A new suggestion would be apprechiated.

-Gunnar

"David Portas" <RE****************************@acm.org> wrote in message
news:yr********************@giganews.com...

SELECT empno, depno,
CASE [date] WHEN '20031017' THEN [date] END AS [date]
FROM Work
WHERE depno = 10

Date is a reserved word and shouldn't be used as a column name (it's a
pretty meaningless name for a column anyway - Date of what?)

--
David Portas
------------
Please reply only to the newsgroup
--

[David Portas]

OK. Your DDL was missing any keys. Assuming the PK in Emp is empno and in
Work is (empno, date) and that there is an FK constraint on Work (empno NOT
NULL REFERENCES Emp (empno)):

SELECT COALESCE(E.empno,W.empno) AS empno, 10 AS depno, W.[date]
FROM Emp AS E
LEFT JOIN Work AS W
ON W.empno = E.empno AND W.[date] = '20031017'
WHERE E.depno = 10 OR W.depno = 10

--
David Portas
------------
Please reply only to the newsgroup
--

[John Gilson]

"David Portas" <RE****************************@acm.org> wrote in message
news:cN********************@giganews.com...

OK. Your DDL was missing any keys. Assuming the PK in Emp is empno and in
Work is (empno, date) and that there is an FK constraint on Work (empno NOT
NULL REFERENCES Emp (empno)):

SELECT COALESCE(E.empno,W.empno) AS empno, 10 AS depno, W.[date]
FROM Emp AS E
LEFT JOIN Work AS W
ON W.empno = E.empno AND W.[date] = '20031017'
WHERE E.depno = 10 OR W.depno = 10
Hi David, minor aside but the call to COALESCE is unnecessary as E.empno
will do. Nice solution.

So as to not be taking up bandwidth with total triviality, here's another take.

SELECT depno, empno, MAX(work_date) AS work_date
FROM (SELECT empno, depno, "date" AS work_date
FROM Work
UNION ALL
SELECT empno, depno, NULL AS work_date
FROM Emp) AS W
WHERE depno = 10 AND
(work_date = '20031017' OR work_date IS NULL)
GROUP BY depno, empno

Regards,
jag
--
David Portas
------------
Please reply only to the newsgroup
--