Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How do I write following queries?

Posted on 2011-02-17
7
Medium Priority
?
284 Views
Last Modified: 2012-05-11
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)
0
Comment
Question by:pawar_deepak
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 34919841
This looks like a homework problem.  If so, we are not allowed to help you with this.
0
 
LVL 3

Expert Comment

by:rob_AXSNL
ID: 34919976
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 ...
0
 

Author Comment

by:pawar_deepak
ID: 34920085
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...
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 60

Expert Comment

by:HainKurt
ID: 34920144
try:

select * from Test_staff t
group by personID
having count(1) > 1
0
 
LVL 60

Assisted Solution

by:HainKurt
HainKurt earned 2000 total points
ID: 34920157
oops, above does not work. try this:

select personid, count(1) assignments_count
from Test_staff t
group by personID
having count(1) > 1

0
 
LVL 60

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 34920176
second part:

select distinct personID, employmentID
from Test_staff t left join (select personid, count(1) assignments_count
from Test_staff t
group by personID
having count(1) > 1) x on t.personid=x.personid
0
 

Author Comment

by:pawar_deepak
ID: 34920673
Thanks HainKurt.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question