Need help please - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help please

Author

Topic

rengal
Starting Member

8 Posts

Posted - 2008-04-11 : 08:36:15

Ok, so here is the query i am trying to run


SELECT x.EmpName AS head,
       s.EmpName AS employee
FROM   EMPLOYEE AS x
INNER JOIN EMPLOYEE AS s ON s.UnitNo = x.UnitNo
Where x.LEVL = 'Head' AND
s.LEVL = 'Emp';

The table i am using is this one


EMPNO  EMPNAME  UNITNO  JOBCODE  LEVL  

1111   Briggs   2110    1110     Head

1112   Walter   2110    1120     empl
...more entries

I have to produce results like this


head        employee

Briggs      O'Sullivan
Briggs      Walter

Now to produce these results, i have to look at the unitNo's and check which match, then for head column put the unitNo head under it and put LEVL 'empl' employees in that unitNo under the employee column.

Could use some help though as my query isn't giving any results. I don't fully understand how to run inner join queries tbh.

Any help is appreciated, thanks

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-04-11 : 08:47:00

-- This is only going to work if there is only 1 head per unitno


declare @employee table (EMPNO  int, EMPNAME  varchar(50), UNITNO  int, JOBCODE  int, LEVL  varchar(10) )
insert into @employee
select 1111,   'Briggs',   2110,  1110, 'Head' union all
select 1112,   'Walter',   2110,  1120,  'empl' union all
select 1113,   'O''Sullivan',   2110,  1120,  'empl' union all
select 1114,   'Fred',   2120,  1110, 'Head' union all
select 1115,   'Paul',   2120,  1120,  'empl' union all
select 1116,   'John',   2120,  1120,  'empl'

select a.EMPNAME as head, b.EMPNAME as employee from (
		select * 
		from @employee 
		where Levl = 'Head' ) a
	left join (
		select * 
		from @employee 
		where Levl = 'empl' ) b
	ON a.UNITNO = b.UNITNO

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

rengal
Starting Member

8 Posts

Posted - 2008-04-11 : 08:52:45

Thanks mate. Will try it now

EDIT: It's working, thanks mate

Still don't understand how the join queries work though :(

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources