CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Esther
Esther

Reputation: 61

Having Trouble Using "AND" Operator to Display Desired Results

Having difficulty in getting the query to retrieve desired results using the "AND" operator. Need to show the people's firstname, lastname, email, and courseID in districtIDNum=5 who have completed any of the 3 courses, namely (Health Old (courseIDNum=11) OR Health New (courseIDNum=12)) AND (PE Old (courseIDNum=68 OR PE New (courseIDNum=177)) AND courseIDNum=128. Please know that 11 and 12 are actually the same course: one is an old course and the other is new (same with 68 and 177), hence, people in districtIDNum=5 can only take one or the other; that's why I tried using (11, 12). However, it doesn't work correctly as it returns 0 rows.

SELECT max(p.FirstName),
       max(p.LastName),
       max(p.Email),
       max(s.CourseIDNum)
FROM People p
INNER JOIN Registration r
   on p.PeopleID = r.PeopleIdNum
INNER JOIN Section s
   on r.SectionIDNum = s.SectionID
INNER JOIN School sc
   on p.SchoolIDNum = sc.SchoolID
WHERE s.CourseIDNum IN (11, 12)
  AND s.CourseIDNum IN (68, 177)
  AND s.CourseIDNum=128
  AND sc.DistrictIDNum = 5
  AND r.Completed='Y'
group by p.PeopleID
having count(distinct s.CourseIDNum)=1

Row that should NOT be returned is Liz M from districtIDNum=12 who completed courseIDNum=195. On the hand, Esther B from districtIDNum=5 has completed courseIDs 11, 177 and 128; therefore, her data should display all 3 courseIDs. The data should also show that Tommy L has completed courseIDs 12 and 68.

Here are the sample tables:

peopleID   FirstName   LastName   Email                     schoolIDNum
1               Esther         B                [email protected]      33
2               Tommy        L                 [email protected]       55
3               Liz               M                [email protected]     90

registrationID   peopleIDNum   sectionIDNum
22                    1                      40
23                    2                      41
24                    3                      132
25                    1                      78
26                    2                      52
27                    1                      63

sectionID   courseIDNum
40              11
41              12
52              68
63              128
78              177
132            195

courseID   coursename
11             Health (Old)
12             Health (New)
68             PE (Old)
128           Keyboarding
177           PE (New)
195           Computing

schoolID   districtIDNum
33             5
55             5
90             12

Upvotes: 0

Views: 62

Answers (2)

devio
devio

Reputation: 37215

The way you state the query condition

 s.CourseIDNum IN (11, 12)
 AND s.CourseIDNum IN (68, 177)
 AND s.CourseIDNum=128

means that CourseIDNum must equal a set of integers at the same time.

SELECT p.FirstName,
       p.LastName,
       p.Email
FROM People p
WHERE p.PeopleID IN
(
    SELECT r.PeopleIdNum
    FROM Registration r
    INNER JOIN Section s
       on r.SectionIDNum = s.SectionID
    INNER JOIN School sc
       on p.SchoolIDNum = sc.SchoolID
    WHERE s.CourseIDNum IN (11, 12, 68, 177, 128)
      AND sc.DistrictIDNum = 5
      AND r.Completed='Y'
)

Once you have the list of persons, retrieve their CourseIDs by concatenating Section records as illustrated here or here.

Upvotes: 1

Qasim Javaid Khan
Qasim Javaid Khan

Reputation: 660

WHERE s.CourseIDNum IN (11, 12)
  AND s.CourseIDNum IN (68, 177)
  AND s.CourseIDNum=128

Your this portion means you are writing the query something like this

WHERE s.CourseIDNum IN (11,12,68,177) and courseIDNum = 128

This means CourseID num in one case can be equal to 11 , 12, 68, 177 and at the same time it should be equal to 128.... which wont be possible. So i am not sure, but try using like

WHERE (s.CourseIDNum IN (11, 12,68,177)
  OR s.CourseIDNum=128)

Upvotes: 0

Related Questions