asked on

How do I write following queries?

Can you help me writing following queries:

1. How many staff members have more than 1 assignment?
2. Select personID, employmentID from TEST_STAFF table having more than 1 assignment.

CREATE TABLE [Test_staff](
[personID] [int] NOT NULL,
[identityID] [int] NULL,
[employmentID] [int] NULL,
[assignmentID] [int] NULL)

INSERT INTO [Test_staff] VALUES (1,2,3,4)
INSERT INTO [Test_staff] VALUES (1,2,3,1)
INSERT INTO [Test_staff] VALUES (1,2,3,5)
INSERT INTO [Test_staff] VALUES (2,4,6,6)
INSERT INTO [Test_staff] VALUES (3,2,1,1)
INSERT INTO [Test_staff] VALUES (3,2,1,2)
INSERT INTO [Test_staff] VALUES (4,2,1,3)
INSERT INTO [Test_staff] VALUES (5,3,2,1)

knightEknight

This looks like a homework problem. If so, we are not allowed to help you with this.

rob_AXSNL

select t.personID from Test_staff t where 1 <
(select COUNT(PersonID) from Test_staff t2 where t.personID = t2.personID)

select t.personID from Test_staff t group by t.personid having 1 <
(select COUNT(PersonID) from Test_staff t2 where t.personID = t2.personID)

You can play around ...

pawar_deepak

ASKER

Hello KnightEknight,

Thanks for your comment but it surely isn't a homework question... I want to write query which is similar to queries mentioned above. I just created a new table to simplify the question..

I need to perform some operations on following view... I just asked the part which I need help with.

CREATE VIEW [dbo].[staff]
AS
SELECT p.personID, p.stateID, p.staffNumber, p.staffStateID,
i.identityID, i.effectiveDate, i.lastName, i.firstName, i.middleName, i.suffix, i.alias, i.gender, i.birthdate, i.ssn, i.raceEthnicity,
e.employmentID, ea.assignmentID, ea.startDate, ea.endDate, ea.title, e.seniority, ea.type, ea.teacher,
ea.specialEd, ea.behavior, ea.health, ea.advisor, ea.supervisor, ea.foodservice, ea.departmentID,
s.schoolID, s.name schoolName, s.number schoolNumber, d.name departmentName,ea.excludeReferral, ea.counselor
FROM dbo.Person p WITH (NOLOCK)
INNER JOIN dbo.[Identity] i WITH (NOLOCK) ON p.currentIdentityID = i.identityID
LEFT OUTER JOIN dbo.Employment e WITH (NOLOCK) ON e.personID = p.personID
INNER JOIN dbo.EmploymentAssignment ea WITH (NOLOCK) ON p.personID = ea.personID
INNER JOIN dbo.School s WITH (NOLOCK) ON s.schoolID = ea.schoolID
LEFT OUTER JOIN dbo.Department d WITH (NOLOCK) ON d.departmentID = ea.departmentID

It might be a simple question for you to answer but I'm getting errors while writing my query...