JOINing the same table

This is probably something simple I'm missing, but here it is anyway.
I have a table called "Employee". Primary key is "pk_EmployeeID".
There is a foreign key field called "fk_SupervisorID" which relates to
pk_EmployeeID. The object is to pull an employee's supervisor from the
same table.
I can't seem to get past a basic SELECT statement to run more complex
queries. Here's what I'm trying:
SELECT Employee.LastName AS EmpLastName, Sup.LastName AS SupLastName
FROM Employee
' JOIN Employee AS Sup ON Sup.fk_SupervisorID =
Employee.pk_EmployeeID
I've tried inner joins, outer joins, left, right... you name it. The
results I get are always putting the "Employee's" last name in the
Supervisor's (SupLastName) column.
A LEFT JOIN duplicates the supervisors giving me more records than is
actually in the table (which I thought would occur for a RIGHT JOIN)
and a RIGHT JOIN gives me the correct record count, still with botched
name fields. INNER JOIN also botches the name fields but does what it
is supposed to by not including the few records that don't have a
supervisor.
Any ideas?
Thanks in advance!!--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
I believe you should have something like this:
CREATE TABLE Employees (
EmployeeID integer not null primary key ,
Name varchar(20) not null ,
- -- ... other columns ...
SupervisorID integer references Employees (EmployeeID)
)
SELECT E.LastName AS EmpLastName, S.LastName AS SupLastName
FROM Employees As E INNER JOIN Employees AS S
ON E.SupervisorID = S.EmployeeID
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQgfl54echKqOuFEgEQJwFwCggJAWohcqvWQK
QWUNBHdVeliRvDUAoIod
3gHlfBy2yj0p8/J4KRWPjffP
=TMY3
--END PGP SIGNATURE--
Wally wrote:
> This is probably something simple I'm missing, but here it is anyway.
> I have a table called "Employee". Primary key is "pk_EmployeeID".
> There is a foreign key field called "fk_SupervisorID" which relates to
> pk_EmployeeID. The object is to pull an employee's supervisor from the
> same table.
> I can't seem to get past a basic SELECT statement to run more complex
> queries. Here's what I'm trying:
> SELECT Employee.LastName AS EmpLastName, Sup.LastName AS SupLastName
> FROM Employee
> ' JOIN Employee AS Sup ON Sup.fk_SupervisorID =
> Employee.pk_EmployeeID
> I've tried inner joins, outer joins, left, right... you name it. The
> results I get are always putting the "Employee's" last name in the
> Supervisor's (SupLastName) column.
> A LEFT JOIN duplicates the supervisors giving me more records than is
> actually in the table (which I thought would occur for a RIGHT JOIN)
> and a RIGHT JOIN gives me the correct record count, still with botched
> name fields. INNER JOIN also botches the name fields but does what it
> is supposed to by not including the few records that don't have a
> supervisor.|||Are you saying you have a primary key that is been referenced as a
foreignkey in the same table? WHYyyyy' Anyways, to solve your problem
for now, here is the query.....
select E.LastName as EmpLastName,
(Select LastName as SupLastName from Employees Where employeeID =
E.SupervisorID)
from Employee E|||Query Builder wrote:
> Are you saying you have a primary key that is been referenced as a
> foreignkey in the same table? WHYyyyy' Anyways, to solve your
problem
> for now, here is the query.....
No... there is no actual reference between the fields. As far as SQL
Server is concerned, pk_SupervisorID is an indexed primary key.....
and fk_SupervisorID is just another unreferenced column with data in
it. The relation of the two fields only occurs in reports that the
front-end calls for.

> select E.LastName as EmpLastName,
> (Select LastName as SupLastName from Employees
> Where employeeID = E.SupervisorID)
> from Employee E
FYI: Your SELECT statement worked fine, but for some odd reason it
didn't label the SupLastName field.
Thanks!|||> SELECT E.LastName AS EmpLastName, S.LastName
> AS SupLastName
> FROM Employees As E INNER JOIN Employees AS S
> ON E.SupervisorID = S.EmployeeID
That worked too. Thanks!
And as I originally said... it was something small that I was missing.
:-|
Oh well... Thanks again!

Joining and Conditional Column Data Help

I have 2 table that I want to join and output a row on a condition that one of the records have a null in the field. Heres what I have.

employee table (empid, name)
tasks table (taskid, empid, taskname, resolution)

If the resolution is null than I want it to be accounted for in each employee record. Heres my query so far that joins the 2 tables and accounts for each employee and counts each task they have. I need another column that counts the tasks.resolution's null values for each employee but cant figure it out. Thanks for any help!

SELECT e.empid,
e.name,
COUNT(t.ID) as 'tcount'
FROM tasks t
RIGHT JOIN employee e ON c.empid = t.empid
GROUP BY e.empid, e.name
order by 'tcount' desc

SELECT

e.empid,

e

.empname,

COUNT

(t.taskid)as'tcount'

FROM

tasks t

LEFT

JOIN employee eON e.empid= t.empid

WHERE

t.resolutionISNULL

GROUP

BY e.empid, e.empname

Order

by tcountDesc|||

limno's query doesn't quite work the way you'd expect. Because it is being filtered by where the resolution is null from within the WHERE clause, it will eliminate employees that have no records where resolution is null from the output. If you want the employees listed even if they have no tasks, then use this instead:

SELECT empid, empname, (SELECTCOUNT(*)FROM tasksWHERE tasks.empid=employee.empidAND resolutionISNULL)as'tcount'FROM employeeOrder by tcountDesc

|||

Thanks. That helped me put together someting else

selecte.empid, e.ename, count(*) as 'tcount', sum(case when t.resolution isnull and t.empid is not null then 1 else 0 end) as 'NULL resolution'
from employee as e
left join tasks as t on t.empid = e.empid
group by e.empid, e.ename
order by 3 desc

Join using like?

I would like to create a view that shows each employee's id and there vp's information, if the vp's id is a part of the dbo.HIERARCHY.Hierarchy field (which is a string containing the employee's entire hierarchy).
In enterprise manager it accepts this as a parsable query, but it isn't getting me the data I need. I suspect it has something to do with me not knowing how to add the wildcard characters when searching the dbo.HIERARCHY.Hierarchy field. Any idea how I can fix this?

SELECT dbo.CCINFORMATION.*, dbo.HIERARCHY.AWID AS Employee
FROM dbo.CCINFORMATION INNER JOIN
dbo.HIERARCHY ON dbo.CCINFORMATION.AWID LIKE dbo.HIERARCHY.Hierarchy
WHERE (dbo.CCINFORMATION.Title LIKE '%VP,%') OR
(dbo.CCINFORMATION.Title LIKE '%CEO%') OR
(dbo.CCINFORMATION.Title LIKE '%VICE PR%')The problem is in your join clause:

ON dbo.CCINFORMATION.AWID LIKE dbo.HIERARCHY.Hierarchy

Since you are not using wildcards, your criteria is equivalent to

ON dbo.CCINFORMATION.AWID = dbo.HIERARCHY.Hierarchy

...except for ignoring trailing spaces in AWID.

Try:

ON dbo.CCINFORMATION.AWID LIKE '%' + dbo.HIERARCHY.Hierarchy + '%'

...but I suspect there are more problems in your table design that need to be addressed.

blindman|||Originally posted by blindman
...but I suspect there are more problems in your table design that need to be addressed.

blindman

Blindman, what makes you thinking that?

dyingjoy: if hierarchy contains your employee ID, you have to reverse your join condition:

ON dbo.HIERARCHY.Hierarchy LIKE '%' + dbo.CCINFORMATION.AWID + '%'|||Blindman, what makes you thinking that?

I'm no expert, but I think that for a one time search this query would work fine. As part of a process however, it would be much more efficient to definitavely identify employess as a 'CEO' or 'VP' in a seperate column.

From the query it appears the Title values are something like 'VP of Marketing', 'VP of Finance', etc. For frequent searches on specific portions of this column, it would meake sense to me to standardize the portion you are searching on in its own column.

ie.

CCINFORMATION.EmpType CHAR(3)
CCINFORMATION.Title VARCHAR(20)|||Yup.

Hard-coding strings like 'VP', 'CEO', 'VICE PR' just invites problems when someone enters a title like 'V.P.', 'C.E.O.', or 'Big Kahuna', or when some mid-level manager or department head requests to be included.

blindman

Join two same tables

Hi,
I have an employee table. It has fields id, type, ctype1, ctype2.
the data in the table can be like this
1 A 1 1
2 A 1 2
3 A 2 1
4 B 1 1
5 B 2 2
I need a query that would give the below result
1 A 1 1
2 A 1 2
3 A 2 1
5 B 2 2
Get all rows of type A and missing rows from type B
Thanks
KiranKiran,
In SQL Server 2005, you can do this simply
and efficiently as follows:
select id, type, ctype1, ctype2
from (
select
id, type, ctype1, ctype2,
rank() over (partition by ctype1, ctype2 order by type) as rk
from employees
) as T
where rk = 1
In SQL Server 2000, try something like this:
select * from employees E1
where type = 'A'
or not exists (
select * from employees as E2
where E2.type = 'A'
and E2.ctype1 = E1.ctype1
and E2.ctype2 = E1.ctype2
)
-- Steve Kass
-- Drew University
Kiran wrote:

> Hi,
> I have an employee table. It has fields id, type, ctype1, ctype2.
> the data in the table can be like this
> 1 A 1 1
> 2 A 1 2
> 3 A 2 1
> 4 B 1 1
> 5 B 2 2
> I need a query that would give the below result
> 1 A 1 1
> 2 A 1 2
> 3 A 2 1
> 5 B 2 2
> Get all rows of type A and missing rows from type B
>
> Thanks
> Kiran|||Try,
select *
from t1
where c2 = 'a'
union all
select *
from t1 as a
where c2 = 'b' and
not exists(
select *
from t1 as b
where b.c2 = 'a' and b.c3 = a.c3 and b.c4 = a.c4
)
go
AMB
"Kiran" wrote:

> Hi,
> I have an employee table. It has fields id, type, ctype1, ctype2.
> the data in the table can be like this
> 1 A 1 1
> 2 A 1 2
> 3 A 2 1
> 4 B 1 1
> 5 B 2 2
> I need a query that would give the below result
> 1 A 1 1
> 2 A 1 2
> 3 A 2 1
> 5 B 2 2
> Get all rows of type A and missing rows from type B
>
> Thanks
> Kiran
>|||Alejandro Mesa wrote:
> Try,
> select *
> from t1
> where c2 = 'a'
> union all
> select *
> from t1 as a
> where c2 = 'b' and
> not exists(
> select *
> from t1 as b
> where b.c2 = 'a' and b.c3 = a.c3 and b.c4 = a.c4
> )
> go
>
> AMB
>
> "Kiran" wrote:
>
Thanks Alejandro Mesa.|||Steve Kass wrote:
> Kiran,
> In SQL Server 2005, you can do this simply
> and efficiently as follows:
> select id, type, ctype1, ctype2
> from (
> select
> id, type, ctype1, ctype2,
> rank() over (partition by ctype1, ctype2 order by type) as rk
> from employees
> ) as T
> where rk = 1
> In SQL Server 2000, try something like this:
> select * from employees E1
> where type = 'A'
> or not exists (
> select * from employees as E2
> where E2.type = 'A'
> and E2.ctype1 = E1.ctype1
> and E2.ctype2 = E1.ctype2
> )
> -- Steve Kass
> -- Drew University
> Kiran wrote:
>
Hi Steve,
I am using sql 2000.
If I add one more to the table, I have the data as
1 A 1 1
2 A 1 2
3 A 2 1
4 B 1 1
5 B 2 2
6 C 3 1
and using your query return me this
1 A 1 1
2 A 1 2
3 A 2 1
5 B 2 2
7 C 3 1
I need all rows of A and missing rows from B.
so I am using this query as of now
SELECT *
FROM employee
WHERE type = 'a'
UNION ALL
SELECT *
FROM employee AS a
WHERE type = 'b' AND NOT EXISTS
(SELECT *
FROM employee AS b
WHERE b.type = 'a' AND b.ctype1 =
a.ctype1 AND b.ctype2 = a.ctype2)
let me know if there is a better way of doing this and is the above
query ok performance wise.
Thanks
Kiran

Join Question

Hi
I drop table employee
go
create table employee
( fname char(20),
lname char(36),
dept char(6),
in_dt char(6)
)
insert into employee values ('Joe','Doe','legal','980622')
insert into employee values ('Joe','Doe','legal','990313')
insert into employee values ('Joe','Doe','legal','990704')
insert into employee values ('Joe','Doe','legal','991015')
insert into employee values ('Joe','Doe','legal','000329')
insert into employee values ('Joe','Doe','legal','010503')
drop table work_day
go
create table work_day
(fname char(20),
lname char(36),
dept char(6),
out_dt char(6)
)
insert into work_day values ('Joe','Doe','legal','990228')
insert into work_day values ('Joe','Doe','legal','000617')
insert into work_day values ('Joe','Doe','legal','010407')
select c.fname,
c.lname,
c.dept,
c.in_dt,
e.out_dt
from employee c
left join work_day e
on c.fname = e.fname
and c.lname=e.lname
and c.dept = e.dept
and convert(datetime,c.in_dt) < convert(datetime,e.out_dt)
go
I am getting the multiple records
fname lname dept in_dt
out_dt
-- -- -- -- --
--
Joe Doe legal 980622
990228
Joe Doe legal 980622
000617
Joe Doe legal 980622
010407
Joe Doe legal 990313
000617
Joe Doe legal 990313
010407
Joe Doe legal 990704
000617
Joe Doe legal 990704
010407
Joe Doe legal 991015
000617
Joe Doe legal 991015
010407
Joe Doe legal 000329
000617
Joe Doe legal 000329
010407
Joe Doe legal 010503 NULL
and I need the following output
fname lname dept in_dt
out_dt
-- -- -- -- --
--
Joe Doe legal 980622
990228
Joe Doe legal 990313 NULL
Joe Doe legal 990704 NULL
Joe Doe legal 991015
000617
Joe Doe legal 000329
010407
Joe Doe legal 010503 NULL
Any Suggestions
AjHello,
Thank you for including DDL, sample data and expected result.
However, there are a few problems:
1. Your DDL does not include primary keys (and other constraints)
2. You use char(6) instead of datetime. That's really bad, for (at
least) two reasons:
- performance: converting the values to datetime prevents SQL Server
from using indexes
- data integrity: in a char(6) you can store a value that is not a
valid date and you won't notice until it's too late
3. The expected result... is not quite what I expected. Either the
provided expected result is be wrong or I am unable to understand what
it should contain. If the expected result would have been this:
fname lname dept in_dt out_dt
-- -- -- -- --
Joe Doe legal 980622 990228
Joe Doe legal 990313 NULL
Joe Doe legal 990704 NULL
Joe Doe legal 991015 NULL
Joe Doe legal 000329 000617
Joe Doe legal 010503 NULL
Then a possible solution is this:
SELECT fname, lname, dept, in_dt, (
SELECT MIN(out_dt)
FROM work_day e
WHERE e.fname=c.fname and e.lname=c.lname
AND CONVERT(datetime,e.out_dt)>CONVERT(datetime,c.in_dt)
AND NOT EXISTS (
SELECT *
FROM employee d
WHERE d.fname=c.fname and d.lname=c.lname
AND CONVERT(datetime,d.in_dt)>CONVERT(datetime,c.in_dt)
AND CONVERT(datetime,d.in_dt)<CONVERT(datetime,e.out_dt)
)
) AS out_dt
FROM employee c
Razvan|||Thank you, I agree with the char date field but that is what the table was
initially created with and I am extracting data from it. Your script gave
me the output I will looking for.
Aj
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1116436704.952637.73930@.g49g2000cwa.googlegroups.com...
> Hello,
> Thank you for including DDL, sample data and expected result.
> However, there are a few problems:
> 1. Your DDL does not include primary keys (and other constraints)
> 2. You use char(6) instead of datetime. That's really bad, for (at
> least) two reasons:
> - performance: converting the values to datetime prevents SQL Server
> from using indexes
> - data integrity: in a char(6) you can store a value that is not a
> valid date and you won't notice until it's too late
> 3. The expected result... is not quite what I expected. Either the
> provided expected result is be wrong or I am unable to understand what
> it should contain. If the expected result would have been this:
> fname lname dept in_dt out_dt
> -- -- -- -- --
> Joe Doe legal 980622 990228
> Joe Doe legal 990313 NULL
> Joe Doe legal 990704 NULL
> Joe Doe legal 991015 NULL
> Joe Doe legal 000329 000617
> Joe Doe legal 010503 NULL
> Then a possible solution is this:
> SELECT fname, lname, dept, in_dt, (
> SELECT MIN(out_dt)
> FROM work_day e
> WHERE e.fname=c.fname and e.lname=c.lname
> AND CONVERT(datetime,e.out_dt)>CONVERT(datetime,c.in_dt)
> AND NOT EXISTS (
> SELECT *
> FROM employee d
> WHERE d.fname=c.fname and d.lname=c.lname
> AND CONVERT(datetime,d.in_dt)>CONVERT(datetime,c.in_dt)
> AND CONVERT(datetime,d.in_dt)<CONVERT(datetime,e.out_dt)
> )
> ) AS out_dt
> FROM employee c
> Razvan
>

Join multiple tables

I'm trying to join 3 tables:

EMPLOYEE - empid

SKILL - empid, skillid, skill

SKILLOPTIONS - skillid, option

An EMPLOYEE will always have at least 1 SKILL but each SKILL may or may not have any SKILLOPTIONS. I do an INNER JOIN:

EMPLOYEE->SKILL->SKILLOPTIONS but I only get a record if there is actually a SKILLOPTION. I want a record with EMPLOYEE and SKILL even if there are no SKILLOPTIONS. In Oracle it is the (+) symbol in the WHERE statement in conjunction with the JOIN. Am new to this so I'm sure the answer is simple.

Try left join, like

EMPLOYEE left join SKILL on EMPLOYEE.empid= SKILL.empid

left join SKILLOPTIONS on SKILLOPTIONS left join SKILL on SKILLOPTIONS=skillid=SKILL.skillid

|||

Biff:

I think that if you would associate the Oracle + operator only with the SKILLOPTIONS table that the join that you want is similar to:

select <selectList>
from EMPLOYEE
inner join SKILL
on EMPLOYEE.empid = SKILL.empid
left outer join SKILLOPTIONS
on SKILL.skillid = SKILLOPTIONS.skillid

Dave