Match in two tables - 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

Match in two tables

Author

Topic

JBee
Starting Member

9 Posts

Posted - 2007-12-28 : 10:33:32

Hi, hope someone can help.

I have a table of employees and two sites. Most employees only do shifts at the one site but may occasionally work at the other site. What i want is the details of those who have worked at both sites.

for e.g.

Employees
1 Dave
2 Peter
3 John

Site 1
Mon John
Tue Peter
Wed John
Thu John
Fri Peter

Site 2
Mon Dave
Tue Dave
Wed Dave
Thu Dave
Fri Peter

So the answer should be 2 Peter. Hope this explains the problem. Probably very obvious but it has me stumped.

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-28 : 10:56:40

what have you tried so far

Be One with the Optimizer
TG

JBee
Starting Member

9 Posts

Posted - 2007-12-28 : 11:25:05

Well so far i have this:

SELECT staffid, name, COUNT(Site1.Staffid) as total
FROM staff INNER JOIN Site1 ON staff.staffid = site1.staffid
GROUP BY staffid, name

Which gets me a count of how many shifts each member of staff have done at one site (site1).

this seems a far cry from what i want!

JBee
Starting Member

9 Posts

Posted - 2007-12-28 : 11:43:51

I think i have solved it below - but can't try just yet. Anyway hopefully that will do it first thing am.

select staff.staffid, staff.name
from staff inner join site1 on staff.staffid = site1.staffid
inner join site2 on staff.staffid = site2.staffid
where site1.staffid = site2.staffid

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-28 : 11:53:10

you just about have it if you combine your two solutions:


select	staff.staffid
	,staff.name
from staff inner join site1 on staff.staffid = site1.staffid
inner join site2 on staff.staffid = site2.staffid
group by staff.staffid
	,staff.name

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources