SQL help pls - 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

SQL Server 2000 Forums

Transact-SQL (2000)

SQL help pls

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2007-02-28 : 09:19:48

I can't seem to figure out how to do this - I hope this makes sense:
Requirement:
Find all EmpNames from Table1 for John Doe where the Department (table1) = DepartmentAccess for JohnDoe in table 2

Table1:
EmpID, EmpName, Department
1 John Doe Operations
2 Bob Smith Finance
3 Al Green Training
4 Mark Lee IT

Table2:
EmpID, DepartmentAccess
1 Accounting
1 Finance
1 Training

Result for John Doe:
Bob Smith
Al Green

Can anyone help? thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-28 : 09:54:27

[code]
declare @Table1 table
(
EmpID int,
EmpName varchar(20),
Department varchar(20)
)
insert into @Table1
select 1, 'John Doe', 'Operations' union all
select 2, 'Bob Smith', 'Finance' union all
select 3, 'Al Green', 'Training' union all
select 4, 'Mark Lee', 'IT'

declare @Table2 table
(
EmpID int,
DepartmentAccess varchar(20)
)
insert into @Table2
select 1, 'Accounting' union all
select 1, 'Finance' union all
select 1, 'Training'

select t1.EmpName
from @Table1 t1 inner join @Table2 t2
on t1.Department = t2.DepartmentAccess
where t2.EmpID = 1
[/code]

ann
Posting Yak Master

220 Posts

Posted - 2007-02-28 : 10:05:19

I don't understand something. In your example you have values hardcoded:
insert into @Table1
select 1, 'John Doe', 'Operations' union all
select 2, 'Bob Smith', 'Finance' union all
select 3, 'Al Green', 'Training' union all
select 4, 'Mark Lee', 'IT'

how would I do those inserts without hardcoding those values as those values will obviously change and I wouldn't know what they always were?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-28 : 10:09:49

He is just setting up a test environment with the values you provided.
This is for testing a suggestion based on the same values you have.

What YOU NEED TO DO, is to replace @table1 table name with the table name you have in YOUR environment.

Peter Larsson
Helsingborg, Sweden

ann
Posting Yak Master

220 Posts

Posted - 2007-02-28 : 10:13:46

ohhhh...LOL! I feel like such an idiot!!!

Thank you both for helping ....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources