No way out please help

I am trying to resolve this issue for more than 3 hours.
I have two tables Emp table and dept course table. I need to find employee
who haven't took the course required by department or employee who already
took all the required courses by dept.
Employee table has this data
emp_id name course dept_id
1 T VB 1
1 T Access 1
1 T FOXPro 1
2 M Network 2
2 M Lan 2
Dept Course Table
dept Course
1 Vb
1 Access
1 SQL
1 Database
1 C Sharp
2 Network
2 Lan
the result will be
emp_id name course Taken dept_id Corse Required
1 T VB 1 VB
1 T Access 1 Access
1 T FOXPro 1 Null
1 T NULL 1
Database
1 T NULL 1 C
sharp
2 M Network 2 Network
2 M Lan 2 Lan
Thanks in advance.
Tanweer
thussain@.appleone.comTanweer,
How about ...
select
emp.emp_id,
emp.name,
taken.course,
coalesce(required.dept,taken.dept),
required.course
from
emp, dept taken, dept required
where
(empt.dept = taken.dept) or
(empt.dept = required.dept)
group by
emp.emp_id, emp.name,
taken.course,
required.dept, required.course
-- Alex Papadimoulis
"Tanweer" wrote:

> I am trying to resolve this issue for more than 3 hours.
> I have two tables Emp table and dept course table. I need to find employee
> who haven't took the course required by department or employee who already
> took all the required courses by dept.
> Employee table has this data
> emp_id name course dept_id
> 1 T VB 1
> 1 T Access 1
> 1 T FOXPro 1
> 2 M Network 2
> 2 M Lan 2
> Dept Course Table
> dept Course
> 1 Vb
> 1 Access
> 1 SQL
> 1 Database
> 1 C Sharp
> 2 Network
> 2 Lan
>
> the result will be
> emp_id name course Taken dept_id Corse Required
> 1 T VB 1 VB
> 1 T Access 1 Access
> 1 T FOXPro 1 Null
> 1 T NULL 1
> Database
> 1 T NULL 1
C
> sharp
> 2 M Network 2 Network
> 2 M Lan 2 Lan
>
> Thanks in advance.
> Tanweer
> thussain@.appleone.com|||Thanks for fast response
I only have two table Emp and Course required
I am confuse about this statement
from emp, dept taken, dept required
Thanks
Tanweer
"Alex Papadimoulis" wrote:
> Tanweer,
> How about ...
>
> select
> emp.emp_id,
> emp.name,
> taken.course,
> coalesce(required.dept,taken.dept),
> required.course
> from
> emp, dept taken, dept required
> where
> (empt.dept = taken.dept) or
> (empt.dept = required.dept)
> group by
> emp.emp_id, emp.name,
> taken.course,
> required.dept, required.course
>
> -- Alex Papadimoulis
>
> "Tanweer" wrote:
>|||Select E.emp_id,
E.name, E.Dept, C.course,
Case When Exists
(Select * From Emp
Where Emp_ID = E.Emp_ID
And Course = C.Course)
Then 1 Else 0 End Taken,
Case When E.Dept = C.Dept
Then 1 Else 0 End Required
From (Select Distinct emp_ID,
Name, Dept
From Emp) As E
Cross Join DeptCourse As C
"Tanweer" wrote:

> I am trying to resolve this issue for more than 3 hours.
> I have two tables Emp table and dept course table. I need to find employee
> who haven't took the course required by department or employee who already
> took all the required courses by dept.
> Employee table has this data
> emp_id name course dept_id
> 1 T VB 1
> 1 T Access 1
> 1 T FOXPro 1
> 2 M Network 2
> 2 M Lan 2
> Dept Course Table
> dept Course
> 1 Vb
> 1 Access
> 1 SQL
> 1 Database
> 1 C Sharp
> 2 Network
> 2 Lan
>
> the result will be
> emp_id name course Taken dept_id Corse Required
> 1 T VB 1 VB
> 1 T Access 1 Access
> 1 T FOXPro 1 Null
> 1 T NULL 1
> Database
> 1 T NULL 1
C
> sharp
> 2 M Network 2 Network
> 2 M Lan 2 Lan
>
> Thanks in advance.
> Tanweer
> thussain@.appleone.com|||On Fri, 18 Mar 2005 12:57:10 -0800, Tanweer wrote:

>I am trying to resolve this issue for more than 3 hours.
(snip)
Hi Tanweer,
Looks like a FULL OUTER JOIN to me:
SELECT e.emp_id, e.name,
e.course AS CourseTaken,
COALESCE(dc.dept_id, e.dept_id) AS dept_id,
dc.course as CourseRequired
FROM Employees AS e
FULL OUTER JOIN DeptCourses AS dc
ON dc.dept_id = e.dept_id
(Untested. For a tested solution, post CREATE TABLE and INSERT
statements - see www.aspfaq.com/5006)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||There are some problems with your initial post. Based on your sample data
"FoxPro" course does not belong to any departments, but your expected
results shows it as if it belongs to Dept 1? What determines employee 1
should take courses from department 1? Can you post your DDLs which match
this sample data? What are the keys in your tables?
SELECT e1.Emp_id, e1.Emp_name,
ec1.Course AS "course Taken",
de1.dept_id, dc1.Course AS "Course Required"
FROM DeptCourses dc1
INNER JOIN ( SELECT DISTINCT Emp_id, Dept_id
FROM Employees ) de1
ON dc1.Dept_id = de1.Dept_id
FULL OUTER JOIN ( SELECT DISTINCT Emp_id, course
FROM Employees ) ec1
ON de1.Emp_id = ec1.Emp_id
AND dc1.Course = ec1.Course
INNER JOIN ( SELECT DISTINCT emp_id, "name"
FROM Employees ) e1 ( emp_id, Emp_name )
ON e1.Emp_id = COALESCE( de1.Emp_id, ec1.Emp_id )
ORDER BY e1.Emp_id ;
On an initial glance the schema seems under-normalized. One generic sign of
a poorly designed schema requires complicated relational expressions to
answer simple queries. For instance, if dept can be determined by the course
it offers, having the department identifier in the Employee table is
redundant. Based on some obvious assumptions, to correct the design flaw,
you should remove the dept_id column in the Employee table. You should have
an "association" table which models the relationship between an employee and
a department. Depending on your business rules, the same issue may be
applicable for the Employee name column as well.
CREATE TABLE Employees (
Emp_id INT NOT NULL PRIMARY KEY,
Emp_name VARCHAR(40)
) ;
CREATE TABLE DeptCourses (
Dept_id INT NOT NULL,
--REFERENCES Departments( dept_id )
Course VARCHAR(20) NOT NULL
PRIMARY KEY ( Dept_id, Course )
) ;
CREATE TABLE EmployeeCourses (
Emp_id INT NOT NULL
REFERENCES Employees( Emp_id ),
Course VARCHAR(20) NOT NULL
PRIMARY KEY ( Emp_id, Course )
);
CREATE TABLE DeptEmployees (
dept_id INT NOT NULL,
-- REFERENCES Departments( dept_id ),
Emp_id INT NOT NULL
REFERENCES Employees( Emp_id )
PRIMARY KEY ( dept_id, Emp_id )
);
Now add the sample data:
INSERT Employees SELECT 1, 'T' ;
INSERT Employees SELECT 2, 'M' ;
INSERT DeptCourses SELECT 1 ,'VB' ;
INSERT DeptCourses SELECT 1 ,'Access' ;
INSERT DeptCourses SELECT 1 ,'SQL' ;
INSERT DeptCourses SELECT 1 ,'Database' ;
INSERT DeptCourses SELECT 1 ,'C Sharp' ;
INSERT DeptCourses SELECT 2 ,'Network' ;
INSERT DeptCourses SELECT 2 ,'Lan' ;
INSERT EmployeeCourses SELECT 1, 'VB' ;
INSERT EmployeeCourses SELECT 1, 'Access' ;
INSERT EmployeeCourses SELECT 1, 'FOXPro' ;
INSERT EmployeeCourses SELECT 2, 'Network' ;
INSERT EmployeeCourses SELECT 2, 'Lan' ;
INSERT DeptEmployees SELECT 1, 1 ;
INSERT DeptEmployees SELECT 2, 2 ;
Now you can have:
SELECT e1.Emp_id, e1.Emp_name,
ec1.Course AS "course Taken",
de1.dept_id, dc1.Course AS "Course Required"
FROM DeptCourses dc1
INNER JOIN DeptEmployees de1
ON dc1.Dept_id = de1.Dept_id
FULL OUTER JOIN EmployeeCourses ec1
ON de1.Emp_id = ec1.Emp_id
AND dc1.Course = ec1.Course
INNER JOIN Employees e1
ON e1.Emp_id = COALESCE( de1.Emp_id, ec1.Emp_id )
ORDER BY e1.Emp_id ;
Now try different queries, most of them can be answered with simpler SQL
formulations.
Anith|||I am sorry to be lazy,. Here I posted the whole
/*
drop table #temp
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
--give me only the course taken and have valid code
select distinct t.people_id,t.course_taken,
t.date_taken,t.full_name,t.dept_taken,t.jobcode_taken
from #temp t, #temp1 t1
where dept_taken=t1.deptid
and t.jobcode_taken=t1.jobcode_id
I like to see it
people_id Course Name Required Taken Dept Job Code
3223 Access Doney,Vicke 0 1 3 7
3223 VB Doney,Vicke 0 1 3 7
3223 FoxPro Doney,Vicke 1 1 3 7
3223 SQL DBA Doney,Vicke 1 1 3 7
3224 SQL DBA Ricky Yo 0 1 4 8
3224 VB Ricky Yo 0 1 4 8
select * from #temp
select * from #temp1
Thanks for all the help
Tanweer
"Hugo Kornelis" wrote:

> On Fri, 18 Mar 2005 12:57:10 -0800, Tanweer wrote:
>
> (snip)
> Hi Tanweer,
> Looks like a FULL OUTER JOIN to me:
> SELECT e.emp_id, e.name,
> e.course AS CourseTaken,
> COALESCE(dc.dept_id, e.dept_id) AS dept_id,
> dc.course as CourseRequired
> FROM Employees AS e
> FULL OUTER JOIN DeptCourses AS dc
> ON dc.dept_id = e.dept_id
> (Untested. For a tested solution, post CREATE TABLE and INSERT
> statements - see www.aspfaq.com/5006)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Did u try this?
Select E.emp_id,
E.name, E.Dept, C.course,
Case When Exists
(Select * From Emp
Where Emp_ID = E.Emp_ID
And Course = C.Course)
Then 1 Else 0 End Taken,
Case When E.Dept = C.Dept
Then 1 Else 0 End Required
From (Select Distinct emp_ID,
Name, Dept
From Emp) As E
Cross Join DeptCourse As C
"Tanweer" wrote:
> I am sorry to be lazy,. Here I posted the whole
> /*
> drop table #temp
> 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
> --give me only the course taken and have valid code
> select distinct t.people_id,t.course_taken,
> t.date_taken,t.full_name,t.dept_taken,t.jobcode_taken
> from #temp t, #temp1 t1
> where dept_taken=t1.deptid
> and t.jobcode_taken=t1.jobcode_id
> I like to see it
> people_id Course Name Required Taken Dept Job Code
> 3223 Access Doney,Vicke 0 1 3 7
> 3223 VB Doney,Vicke 0 1 3 7
> 3223 FoxPro Doney,Vicke 1 1 3 7
> 3223 SQL DBA Doney,Vicke 1 1 3 7
> 3224 SQL DBA Ricky Yo 0 1 4 8
> 3224 VB Ricky Yo 0 1 4 8
>
> select * from #temp
> select * from #temp1
> Thanks for all the help
> Tanweer
> "Hugo Kornelis" wrote:
>|||I used it this way
Select E.people_id,
E.full_name, E.Dept_taken, C.coursecode,
Case When Exists
(Select * From #temp
Where people_ID = E.people_ID
And Course_taken = C.Coursecode)
Then 1 Else 0 End Taken,
Case When E.Dept_taken = C.Deptid
Then 1 Else 0 End Required
From (Select Distinct people_ID,
full_Name, Dept_taken
From #temp) As E
Cross Join #temp1 As C
However the result is not coming up correctly
3223 Doney,Vicke 3 SQL DBA 1 1
3223 Doney,Vicke 3 FoxPro 1 1
3223 Doney,Vicke 3 FoxPro 1 0
3223 Doney,Vicke 3 ORACLE 0 1
3224 Ricky Yo 4 SQL DBA 1 0
3224 Ricky Yo 4 FoxPro 0 0
3224 Ricky Yo 4 FoxPro 0 1
3224 Ricky Yo 4 ORACLE 0 0
Line 3,4 should not be there because they are only require for dept 4 and
also it is not showing any course taken by the employee however it was not
required.
Thanks for all the help
"CBretana" wrote:
> Did u try this?
> Select E.emp_id,
> E.name, E.Dept, C.course,
> Case When Exists
> (Select * From Emp
> Where Emp_ID = E.Emp_ID
> And Course = C.Course)
> Then 1 Else 0 End Taken,
> Case When E.Dept = C.Dept
> Then 1 Else 0 End Required
> From (Select Distinct emp_ID,
> Name, Dept
> From Emp) As E
> Cross Join DeptCourse As C
>
> "Tanweer" wrote:
>|||Your sample data, there was only one row per course in the Dept Course table
.
Your results indicate that the 'FoxPro' course must be in the table more
than once (twice actually). That is why the results show the row twice. Th
e
following makes the adjustment for that...
Select E.people_id,
E.full_name, E.Dept_taken,
C.coursecode,
Case When Exists
(Select * From #temp
Where people_ID = E.people_ID
And Course_taken = C.Coursecode)
Then 1 Else 0 End Taken,
Case When Exists
(Select * From #temp1
Where coursecode = E.Course_taken
And Deptid = Dept_taken)
Then 1 Else 0 End Required
From (Select Distinct people_ID,
full_Name, Dept_taken
From #temp) As E
Cross Join
(Select Distinct Course
from #Temp1) As C
"Tanweer" wrote:
> I used it this way
> Select E.people_id,
> E.full_name, E.Dept_taken, C.coursecode,
> Case When Exists
> (Select * From #temp
> Where people_ID = E.people_ID
> And Course_taken = C.Coursecode)
> Then 1 Else 0 End Taken,
> Case When E.Dept_taken = C.Deptid
> Then 1 Else 0 End Required
> From (Select Distinct people_ID,
> full_Name, Dept_taken
> From #temp) As E
> Cross Join #temp1 As C
>
> However the result is not coming up correctly
> 3223 Doney,Vicke 3 SQL DBA 1 1
> 3223 Doney,Vicke 3 FoxPro 1 1
> 3223 Doney,Vicke 3 FoxPro 1 0
> 3223 Doney,Vicke 3 ORACLE 0 1
> 3224 Ricky Yo 4 SQL DBA 1 0
> 3224 Ricky Yo 4 FoxPro 0 0
> 3224 Ricky Yo 4 FoxPro 0 1
> 3224 Ricky Yo 4 ORACLE 0 0
> Line 3,4 should not be there because they are only require for dept 4 and
> also it is not showing any course taken by the employee however it was not
> required.
> Thanks for all the help
>
> "CBretana" wrote:
>