Prev: run a job under job agent
Next: Connecting to subscriber instance via business logic handler...
From: Znubbe on 2 Jul 2008 15:39
Hi all,

Pretty new to this so I would be very glad if any of you could point
me in the right direction.

I have three tables (see below) and I'm trying to get this result:

DepID DepName FirstName LastName
1 Dep1 Andy Ason
2 Dep2 Dave Dson
4 Dep4 Null Null

I do not get the last row. It is the "4 Dep4 Null Null" row i want but
the query does not produce it.

What am I doing wrong?


create table #Department (DepID int NOT NULL PRIMARY KEY,
DepName varchar(50) NOT NULL)
go
INSERT #Department (DepID, DepName)
SELECT 1, 'Dep1' UNION ALL SELECT 2, 'Dep2' UNION ALL SELECT 3, 'Dep3'
UNION ALL SELECT 4, 'Dep4'
go
create table #Department_Person (DepID int NOT NULL,
PersonID int NOT NULL,
AuthorityID int NOT NULL,
PRIMARY KEY (DepID, PersonID))
go
INSERT #Department_Person (DepID, PersonID, AuthorityID)
SELECT 1, 1, 1 union all
SELECT 1, 2, 2 union all
SELECT 1, 3, 2 union all
SELECT 2, 4, 1 union all
SELECT 2, 5, 2 union all
SELECT 2, 6, 2 union all
SELECT 3, 7, 2 union all
SELECT 3, 8, 2
go
CREATE TABLE #Person(PersonID int NOT NULL PRIMARY KEY,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL)
go
INSERT #Person(PersonID, FirstName, LastName)
SELECT 1, 'Andy', 'Ason' union all
SELECT 2, 'Bernie', 'Bson' union all
SELECT 3 , 'Ceasar', 'Cson' union all
SELECT 4 , 'Dave', 'Dson' union all
SELECT 5 , 'Eric', 'Eson' union all
SELECT 6 , 'Freddy', 'Fson' union all
SELECT 7 , 'Grant', 'Gson' union all
SELECT 8 , 'Harry', 'Hson'
go
Select t1.DepID, t1.DepName, t3.FirstName, t3.LastName
FROM #Department t1
LEFT OUTER JOIN #Department_Person t2 ON t1.DepID = t2.DepID
INNER JOIN #Person t3 ON t2.PersonID = t3.PersonID
WHERE t2.AuthorityID = 1

go
drop table #Department, #Department_Person,#Person
From: Alex Kuznetsov on 2 Jul 2008 15:45
On Jul 2, 2:39 pm, Znubbe <znu...(a)hotmail.com> wrote:
> Hi all,
>
> Pretty new to this so I would be very glad if any of you could point
> me in the right direction.
>
> I have three tables (see below) and I'm trying to get this result:
>
> DepID DepName FirstName LastName
> 1 Dep1 Andy Ason
> 2 Dep2 Dave Dson
> 4 Dep4 Null Null
>
> I do not get the last row. It is the "4 Dep4 Null Null" row i want but
> the query does not produce it.
>
> What am I doing wrong?
>
> create table #Department (DepID int NOT NULL PRIMARY KEY,
> DepName varchar(50) NOT NULL)
> go
> INSERT #Department (DepID, DepName)
> SELECT 1, 'Dep1' UNION ALL SELECT 2, 'Dep2' UNION ALL SELECT 3, 'Dep3'
> UNION ALL SELECT 4, 'Dep4'
> go
> create table #Department_Person (DepID int NOT NULL,
> PersonID int NOT NULL,
> AuthorityID int NOT NULL,
> PRIMARY KEY (DepID, PersonID))
> go
> INSERT #Department_Person (DepID, PersonID, AuthorityID)
> SELECT 1, 1, 1 union all
> SELECT 1, 2, 2 union all
> SELECT 1, 3, 2 union all
> SELECT 2, 4, 1 union all
> SELECT 2, 5, 2 union all
> SELECT 2, 6, 2 union all
> SELECT 3, 7, 2 union all
> SELECT 3, 8, 2
> go
> CREATE TABLE #Person(PersonID int NOT NULL PRIMARY KEY,
> FirstName varchar(50) NOT NULL,
> LastName varchar(50) NOT NULL)
> go
> INSERT #Person(PersonID, FirstName, LastName)
> SELECT 1, 'Andy', 'Ason' union all
> SELECT 2, 'Bernie', 'Bson' union all
> SELECT 3 , 'Ceasar', 'Cson' union all
> SELECT 4 , 'Dave', 'Dson' union all
> SELECT 5 , 'Eric', 'Eson' union all
> SELECT 6 , 'Freddy', 'Fson' union all
> SELECT 7 , 'Grant', 'Gson' union all
> SELECT 8 , 'Harry', 'Hson'
> go
> Select t1.DepID, t1.DepName, t3.FirstName, t3.LastName
> FROM #Department t1
> LEFT OUTER JOIN #Department_Person t2 ON t1.DepID = t2.DepID
> INNER JOIN #Person t3 ON t2.PersonID = t3.PersonID
> WHERE t2.AuthorityID = 1
>
> go
> drop table #Department, #Department_Person,#Person

last line should be

WHERE t2.AuthorityID = 1 OR t2.AuthorityID IS NULL

explanations:

http://www.ibm.com/developerworks/db2/library/techarticle/purcell/0112purcell.html

From: Znubbe on 2 Jul 2008 15:54
On Jul 2, 9:45 pm, Alex Kuznetsov <alk...(a)gmail.com> wrote:
> On Jul 2, 2:39 pm, Znubbe <znu...(a)hotmail.com> wrote:
>
>
>
>
>
> > Hi all,
>
> > Pretty new to this so I would be very glad if any of you could point
> > me in the right direction.
>
> > I have three tables (see below) and I'm trying to get this result:
>
> > DepID DepName FirstName LastName
> > 1 Dep1 Andy Ason
> > 2 Dep2 Dave Dson
> > 4 Dep4 Null Null
>
> > I do not get the last row. It is the "4 Dep4 Null Null" row i want but
> > the query does not produce it.
>
> > What am I doing wrong?
>
> > create table #Department (DepID int NOT NULL PRIMARY KEY,
> > DepName varchar(50) NOT NULL)
> > go
> > INSERT #Department (DepID, DepName)
> > SELECT 1, 'Dep1' UNION ALL SELECT 2, 'Dep2' UNION ALL SELECT 3, 'Dep3'
> > UNION ALL SELECT 4, 'Dep4'
> > go
> > create table #Department_Person (DepID int NOT NULL,
> > PersonID int NOT NULL,
> > AuthorityID int NOT NULL,
> > PRIMARY KEY (DepID, PersonID))
> > go
> > INSERT #Department_Person (DepID, PersonID, AuthorityID)
> > SELECT 1, 1, 1 union all
> > SELECT 1, 2, 2 union all
> > SELECT 1, 3, 2 union all
> > SELECT 2, 4, 1 union all
> > SELECT 2, 5, 2 union all
> > SELECT 2, 6, 2 union all
> > SELECT 3, 7, 2 union all
> > SELECT 3, 8, 2
> > go
> > CREATE TABLE #Person(PersonID int NOT NULL PRIMARY KEY,
> > FirstName varchar(50) NOT NULL,
> > LastName varchar(50) NOT NULL)
> > go
> > INSERT #Person(PersonID, FirstName, LastName)
> > SELECT 1, 'Andy', 'Ason' union all
> > SELECT 2, 'Bernie', 'Bson' union all
> > SELECT 3 , 'Ceasar', 'Cson' union all
> > SELECT 4 , 'Dave', 'Dson' union all
> > SELECT 5 , 'Eric', 'Eson' union all
> > SELECT 6 , 'Freddy', 'Fson' union all
> > SELECT 7 , 'Grant', 'Gson' union all
> > SELECT 8 , 'Harry', 'Hson'
> > go
> > Select t1.DepID, t1.DepName, t3.FirstName, t3.LastName
> > FROM #Department t1
> > LEFT OUTER JOIN #Department_Person t2 ON t1.DepID = t2.DepID
> > INNER JOIN #Person t3 ON t2.PersonID = t3.PersonID
> > WHERE t2.AuthorityID = 1
>
> > go
> > drop table #Department, #Department_Person,#Person
>
> last line should be
>
> WHERE t2.AuthorityID = 1 OR  t2.AuthorityID  IS NULL
>
> explanations:
>
> http://www.ibm.com/developerworks/db2/library/techarticle/purcell/011...- Hide quoted text -
>
> - Show quoted text -

Hi Alex,

I have tried that and it still doesn't work. I only get the two first
rows :-(
From: Alex Kuznetsov on 2 Jul 2008 16:17
On Jul 2, 2:54 pm, Znubbe <znu...(a)hotmail.com> wrote:
> On Jul 2, 9:45 pm, Alex Kuznetsov <alk...(a)gmail.com> wrote:
>
>
>
> > On Jul 2, 2:39 pm, Znubbe <znu...(a)hotmail.com> wrote:
>
> > > Hi all,
>
> > > Pretty new to this so I would be very glad if any of you could point
> > > me in the right direction.
>
> > > I have three tables (see below) and I'm trying to get this result:
>
> > > DepID DepName FirstName LastName
> > > 1 Dep1 Andy Ason
> > > 2 Dep2 Dave Dson
> > > 4 Dep4 Null Null
>
> > > I do not get the last row. It is the "4 Dep4 Null Null" row i want but
> > > the query does not produce it.
>
> > > What am I doing wrong?
>
> > > create table #Department (DepID int NOT NULL PRIMARY KEY,
> > > DepName varchar(50) NOT NULL)
> > > go
> > > INSERT #Department (DepID, DepName)
> > > SELECT 1, 'Dep1' UNION ALL SELECT 2, 'Dep2' UNION ALL SELECT 3, 'Dep3'
> > > UNION ALL SELECT 4, 'Dep4'
> > > go
> > > create table #Department_Person (DepID int NOT NULL,
> > > PersonID int NOT NULL,
> > > AuthorityID int NOT NULL,
> > > PRIMARY KEY (DepID, PersonID))
> > > go
> > > INSERT #Department_Person (DepID, PersonID, AuthorityID)
> > > SELECT 1, 1, 1 union all
> > > SELECT 1, 2, 2 union all
> > > SELECT 1, 3, 2 union all
> > > SELECT 2, 4, 1 union all
> > > SELECT 2, 5, 2 union all
> > > SELECT 2, 6, 2 union all
> > > SELECT 3, 7, 2 union all
> > > SELECT 3, 8, 2
> > > go
> > > CREATE TABLE #Person(PersonID int NOT NULL PRIMARY KEY,
> > > FirstName varchar(50) NOT NULL,
> > > LastName varchar(50) NOT NULL)
> > > go
> > > INSERT #Person(PersonID, FirstName, LastName)
> > > SELECT 1, 'Andy', 'Ason' union all
> > > SELECT 2, 'Bernie', 'Bson' union all
> > > SELECT 3 , 'Ceasar', 'Cson' union all
> > > SELECT 4 , 'Dave', 'Dson' union all
> > > SELECT 5 , 'Eric', 'Eson' union all
> > > SELECT 6 , 'Freddy', 'Fson' union all
> > > SELECT 7 , 'Grant', 'Gson' union all
> > > SELECT 8 , 'Harry', 'Hson'
> > > go
> > > Select t1.DepID, t1.DepName, t3.FirstName, t3.LastName
> > > FROM #Department t1
> > > LEFT OUTER JOIN #Department_Person t2 ON t1.DepID = t2.DepID
> > > INNER JOIN #Person t3 ON t2.PersonID = t3.PersonID
> > > WHERE t2.AuthorityID = 1
>
> > > go
> > > drop table #Department, #Department_Person,#Person
>
> > last line should be
>
> > WHERE t2.AuthorityID = 1 OR t2.AuthorityID IS NULL
>
> > explanations:
>
> >http://www.ibm.com/developerworks/db2/library/techarticle/purcell/011...Hide quoted text -
>
> > - Show quoted text -
>
> Hi Alex,
>
> I have tried that and it still doesn't work. I only get the two first
> rows :-(

try this

Select t1.DepID, t1.DepName, t2.FirstName, t2.LastName
FROM #Department t1
LEFT OUTER JOIN (SELECT t2.AuthorityID, t2.DepID, t3.FirstName,
t3.LastName FROM #Department_Person t2
INNER JOIN #Person t3 ON t2.PersonID = t3.PersonID) t2 ON t1.DepID =
t2.DepID
WHERE t2.AuthorityID = 1 OR t2.AuthorityID IS NULL


make sure you've read the article
From: --CELKO-- on 2 Jul 2008 18:18
>> what am I doing wrong? <<

Let's begin with better data element names. Use collective names on
tables because they are sets; use DRI; learn what an identifier is so
you will not say silly things like "authority_id" (it is a value, not
a unique identifier for an entity).

CREATE TABLE Departments
(dept_id INTEGER NOT NULL PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL);

CREATE TABLE JobAssignments -- multiple assignments allowed
(dept_id INTEGER NOT NULL
REFERENCES Departments(dept_id),
emp_id INTEGER NOT NULL
REFERENCES Personnel (emp_id),
authority_lvl INTEGER NOT NULL,
PRIMARY KEY (dept_id, emp_id));

CREATE TABLE Personnel
(emp_id INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL);

From the vague specs you gave, you want to preserve the Departments in
the query, right? Tho you never told us, you want to see only job
assignments at authority level one. Departments goes on the preserved
side of an outer join. You clip out the assignments you want to see
on the unpreserved side. To get the employee names from Personnel,
you need another outer join for the unpreserved employees.

SELECT D.dept_id, D.dept_name, P.first_name, P.last_name
FROM Departments AS D -- preserved table
LEFT OUTER JOIN
(SELECT dept_id, emp_id -- authority 1 only jobs
FROM JobAssignments
WHERE authority_lvl = 1)
AS A (dept_id, emp_id)
ON D.dept_id = A.dept_id
LEFT OUTER JOIN
Personnel AS P -- get the names
ON A.emp_id = P.emp_id;

 |  Next  |  Last
Pages: 1 2
Prev: run a job under job agent
Next: Connecting to subscriber instance via business logic handler...