No way out Please help second post

I am trying to write a query which when run can tell what courses a
department require and what courses were taken by the employee
/*
create table #temp(people_id int,course_taken varchar(50),
date_taken datetime,full_name varchar(50),
assign_id int,dept_taken varchar(50),jobcode_taken varchar(50))
insert into #temp
values(3223,'Access','1/20/1996','Doney,Vicke',6738,'3','7')
insert into #temp values(3223,'VB','11/20/1996','Doney,Vicke',6738,'3','7')
insert into #temp
values(3223,'FoxPro','11/20/1998','Doney,Vicke',6738,'3','7')
insert into #temp values(3223,'SQL
DBA','11/20/1999','Doney,Vicke',6738,'3','7')
insert into #temp values(3224,'SQL DBA','11/20/1999','Ricky Yo',6790,'4','8'
)
insert into #temp values(3224,'VB','11/20/1999','Ricky Yo',6790,'4','8')
create table #temp1(deptid varchar(50),coursecode varchar(50),jobcode_id
varchar(50))
insert into #temp1 values(3,'SQL DBA','7')
insert into #temp1 values(3,'FoxPro','7')
insert into #temp1 values(4,'FoxPro','8')
select * from #temp
select * from #temp1
*/
select coalesce (people_id,null),
coalesce(course_taken,coursecode) as coursecode,
coalesce(dept_taken,deptid) as deptid,
Case When coursecode is null
Then 0 Else 1 End Required,
Case When course_taken is null
Then 0 Else 1 End Taken
from
(
select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
right join #temp1 t1
on t.dept_taken=t1.deptid
and t.course_taken=t1.coursecode
--group by people_id,dept_taken,coursecode,course_t
aken,deptid
union
select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
left outer join #temp1 t1
on t1.deptid=t.dept_taken
and t1.coursecode=t.course_taken
) apple
This is the result set I am getting
How I can show peole id 3224in the first lin so it can show me which
courses was
required and were not taken or courses were taken which were not required.
peopleid Course Dept Required Taken
NULL FoxPro 4 1 0
3223 Access 3 0 1
3223 VB 3 0 1
3223 FoxPro 3 1 1
3223 SQL DBA 3 1 1
3224 SQL DBA 4 0 1
3224 VB 4 0 1
I like to see the outpurt come as this
peopleid Course Dept Required Taken
3224 FoxPro 4 1 0
3223 Access 3 0 1
3223 VB 3 0 1
3223 FoxPro 3 1 1
3223 SQL DBA 3 1 1
3224 SQL DBA 4 0 1
3224 VB 4 0 1
Thanks
TanweeHi
Thanks for posting DDL. But actaully your tables don't have primary key and
store redundant data, you are really need to normalize your database
I'm not certain that understand your requirements. It seems you don't need
to use UNION instead FULL join is giving you the data.
select isnull((select max(people_id) from #temp where
deptid=4),people_id),
coalesce(course_taken,coursecode) as coursecode,
coalesce(dept_taken,deptid) as deptid,
Case When coursecode is null
Then 0 Else 1 End Required,
Case When course_taken is null
Then 0 Else 1 End Taken
from
(
select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
FULL join #temp1 t1
on t.dept_taken=t1.deptid
and t.course_taken=t1.coursecode
) AS APPLE
"Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
news:AD8B48EE-F0F0-4A31-824F-CDCBA60FA3FF@.microsoft.com...
> I am trying to write a query which when run can tell what courses a
> department require and what courses were taken by the employee
>
> /*
> create table #temp(people_id int,course_taken varchar(50),
> date_taken datetime,full_name varchar(50),
> assign_id int,dept_taken varchar(50),jobcode_taken varchar(50))
> insert into #temp
> values(3223,'Access','1/20/1996','Doney,Vicke',6738,'3','7')
> insert into #temp
values(3223,'VB','11/20/ 1996','Doney,Vicke',6738,'3','7')darkred">
> insert into #temp
> values(3223,'FoxPro','11/20/1998','Doney,Vicke',6738,'3','7')
> insert into #temp values(3223,'SQL
> DBA','11/20/1999','Doney,Vicke',6738,'3','7')
> insert into #temp values(3224,'SQL DBA','11/20/1999','Ricky
Yo',6790,'4','8')
> insert into #temp values(3224,'VB','11/20/1999','Ricky Yo',6790,'4','8')
> create table #temp1(deptid varchar(50),coursecode varchar(50),jobcode_id
> varchar(50))
> insert into #temp1 values(3,'SQL DBA','7')
> insert into #temp1 values(3,'FoxPro','7')
> insert into #temp1 values(4,'FoxPro','8')
>
> select * from #temp
> select * from #temp1
> */
> select coalesce (people_id,null),
> coalesce(course_taken,coursecode) as coursecode,
> coalesce(dept_taken,deptid) as deptid,
> Case When coursecode is null
> Then 0 Else 1 End Required,
> Case When course_taken is null
> Then 0 Else 1 End Taken
> from
> (
> select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
> right join #temp1 t1
> on t.dept_taken=t1.deptid
> and t.course_taken=t1.coursecode
> --group by people_id,dept_taken,coursecode,course_t
aken,deptid
> union
> select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
> left outer join #temp1 t1
> on t1.deptid=t.dept_taken
> and t1.coursecode=t.course_taken
> ) apple
>
> This is the result set I am getting
> How I can show peole id 3224in the first lin so it can show me which
> courses was
> required and were not taken or courses were taken which were not required.
>
> peopleid Course Dept Required Taken
> NULL FoxPro 4 1 0
> 3223 Access 3 0 1
> 3223 VB 3 0 1
> 3223 FoxPro 3 1 1
> 3223 SQL DBA 3 1 1
> 3224 SQL DBA 4 0 1
> 3224 VB 4 0 1
>
> I like to see the outpurt come as this
> peopleid Course Dept Required Taken
> 3224 FoxPro 4 1 0
> 3223 Access 3 0 1
> 3223 VB 3 0 1
> 3223 FoxPro 3 1 1
> 3223 SQL DBA 3 1 1
> 3224 SQL DBA 4 0 1
> 3224 VB 4 0 1
>
> Thanks
> Tanwee
>|||This work great as long as another employee don't have the same department.
By adding this statement it will through the previous query
insert into #temp
values(3229,'Access','1/20/2004','Doney,Vicke',6739,'3','7')
"Uri Dimant" wrote:

> Hi
> Thanks for posting DDL. But actaully your tables don't have primary key an
d
> store redundant data, you are really need to normalize your database
> I'm not certain that understand your requirements. It seems you don't need
> to use UNION instead FULL join is giving you the data.
>
> select isnull((select max(people_id) from #temp where
> deptid=4),people_id),
> coalesce(course_taken,coursecode) as coursecode,
> coalesce(dept_taken,deptid) as deptid,
> Case When coursecode is null
> Then 0 Else 1 End Required,
> Case When course_taken is null
> Then 0 Else 1 End Taken
> from
> (
> select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
> FULL join #temp1 t1
> on t.dept_taken=t1.deptid
> and t.course_taken=t1.coursecode
> ) AS APPLE
>
>
> "Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
> news:AD8B48EE-F0F0-4A31-824F-CDCBA60FA3FF@.microsoft.com...
> values(3223,'VB','11/20/1996','Doney,Vicke',6738,'3','7')
> Yo',6790,'4','8')
>
>|||On Mon, 21 Mar 2005 16:31:08 -0800, Tanweer wrote:

>This work great as long as another employee don't have the same department.
>By adding this statement it will through the previous query
> insert into #temp
>values(3229,'Access','1/20/2004','Doney,Vicke',6739,'3','7')
Hi Tanweer,
Upon inspection of your data, I've come to the conclusion that your
problems are caused by lack of normalization. Unless I misunderstand
your data, you should split the #temp table in two parts:
Part 1: people_id, fullname, dept_taken (peoople_id is PK)
Part 2: people_id, course_taken, date_taken (people_id is FK to part 1;
people_id + course_taken is PK)
I didn't want to change your data, so I renamed the temp tables to
permanent tables and created views to mimic how it would look with
normalized data:
create table temp1(people_id int,course_taken varchar(50),
date_taken datetime,full_name varchar(50),
assign_id int,dept_taken varchar(50),jobcode_taken varchar(50))
insert into temp1
values(3223,'Access','1/20/1996','Doney,Vicke',6738,'3','7')
insert into temp1
values(3223,'VB','11/20/1996','Doney,Vicke',6738,'3','7')
insert into temp1
values(3223,'FoxPro','11/20/1998','Doney,Vicke',6738,'3','7')
insert into temp1 values(3223,'SQL
DBA','11/20/1999','Doney,Vicke',6738,'3','7')
insert into temp1 values(3224,'SQL DBA','11/20/1999','Ricky
Yo',6790,'4','8')
insert into temp1 values(3224,'VB','11/20/1999','Ricky
Yo',6790,'4','8')
-- Extra insert statement - uncomment for seecond test case
-- insert into temp1
--values(3229,'Access','1/20/2004','Doney,Vicke',6739,'3','7')
create table temp2(deptid varchar(50),coursecode varchar(50),jobcode_id
varchar(50))
insert into temp2 values(3,'SQL DBA','7')
insert into temp2 values(3,'FoxPro','7')
insert into temp2 values(4,'FoxPro','8')
go
-- Create two views as placeholders for normalized tables
create view people as
select distinct people_id, full_name, assign_id, dept_taken
from temp1
go
create view coursetaken as
select people_id, course_taken, date_taken
from temp1
go
-- Show normalized data
select * from people
select * from coursetaken
go
-- And here's the query
select coalesce(p.people_id, ct.people_id) as people_id,
coalesce(c.coursecode, ct.course_taken) as coursecode,
coalesce(p.dept_taken, (select dept_taken from people where
people_id = coalesce(p.people_id, ct.people_id))) as deptid,
case when c.deptid is null then 0 else 1 end as required,
case when ct.people_id is null then 0 else 1 end as taken
from people as p
inner join temp2 as c
on c.deptid = p.dept_taken
full join coursetaken as ct
on ct.people_id = p.people_id
and ct.course_taken = c.coursecode
go
drop view people
drop view coursetaken
drop table temp1
drop table temp2
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)