0

i am working on college ERP software where i couldn't be able to find solution on following problem. here i am shortly explain the problem

Table Student.

+----+--------+--------+
| id | enroll | sub_id |
+----+--------+--------+
|  1 |      1 |      1 |
|  2 |      1 |      2 |
|  3 |      2 |      1 |
|  4 |      2 |      2 |
|  5 |      2 |      3 |
|  6 |      3 |      1 |
|  7 |      3 |      2 |
+----+--------+--------+

Requirement - In student table, fetch only those enroll no. which have sub_id 1 & 2 if any enroll no. have assign 1,2 & 3 then it should not be come into the result.

I try to us IN function but i won't give right results. Please suggest me solution for this problem. Thanx...

Blank
  • 12,308
  • 1
  • 14
  • 32
Nikhil Bandal
  • 19
  • 2
  • 1
    Show... 1. how you tried to use IN function... 2. what the correct output would look like – dbmitch Jun 29 '16 at 05:04
  • select * from trial where sub_id in (1,2); By using this query i got result enroll 1,2 and 3. But i am expecting result 1 and 3 only because enroll 2 have additional sub_id 3. – Nikhil Bandal Jun 29 '16 at 05:10
  • @Barmar That's not a duplicate question - no need for Group By or Having - sets within sets. This is a basic Select In question – dbmitch Jun 29 '16 at 05:36
  • @dbmitch No it isn't, because he wants to exclude students who have other `sub_id`s. The duplicate question shows how to match exactly a specific set of IDs. – Barmar Jun 29 '16 at 05:39
  • @Barmar - so my basic select does not answer his question? I must really misunderstand what he's asking for. I thought the Select from (select IN) is exactly what he needed. He doesn't mention student ids at all - just enroll nums – dbmitch Jun 29 '16 at 05:42

2 Answers2

0

Using conditional aggregation:

SELECT t1.id, t1.enroll, t1.sub_id
FROM Student t1
INNER JOIN
(
    SELECT enroll,
        SUM(CASE WHEN sub_id IN (1, 2) THEN 1 ELSE 0 END) AS subCount
    FROM Student
    GROUP BY enroll
    HAVING subCount = 2 AND COUNT(*) = 2
) t2
    ON t1.enroll = t2.enroll

The trick in this query is the HAVING clause, which restricts to enrollments which have (1, 2) sub enrolled, and only (1, 2) sub enrolled.

Follow the link below for a running demo:

SQLFiddle

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

I have a hard time figuring out exactly what you want from your description but this returns 1, 3 for your example

SELECT DISTINCT enroll FROM Student
WHERE enroll NOT IN 
(SELECT enroll FROM Student WHERE sub_id NOT IN (1,2))
dbmitch
  • 5,361
  • 4
  • 24
  • 38
  • In my example all enroll have sub_id 1 and 2, but enroll 2 also have sub_id 3, so i need to exclude enroll 2 from result. I required those enroll which only have sub_id 1 and 2. – Nikhil Bandal Jun 29 '16 at 05:45
  • So this works for you? It only returns 1 and 3 as you suggested – dbmitch Jun 29 '16 at 05:46
  • 1
    This doesn't work in this fiddle: http://www.sqlfiddle.com/#!9/bdecd8/1 I added enroll 4 that only has `sub_id = 1` but not `sub_id = 2`. It's included in the result but it shouldn't be. – Barmar Jun 29 '16 at 05:46
  • Gotcha - makes sense to me now! – dbmitch Jun 29 '16 at 05:49