SQL & PL/SQL

Dear SQL-Experts!

Today much of my time I tried to find a query which would show me all students which exactly finished two (three, four, five...) subjects, but NOT all other two subjects, e. g. who finished (Subj A AND Subj B [AND Subj F AND Subj G...]) but NOT (Subj C AND Subj D AND Subj E [AND ...]).
First I tried to get my results like that:

SELECT FPS.NACHNAME, FPS.VORNAME, TO_NUMBER(KLIN_SEM) AS KLIN_SEM FROM FPS
JOIN FST ON FPS.PK_FPS_NR = FST.AK_FPS_NR
JOIN FSL ON FPS.PK_FPS_NR = FSL.FK_FPS_NR_STUDENT
JOIN FSI ON FSI.PK_FSI_NR = FSL.FK_FSI_NR
JOIN KSL ON KSL.PK_KSL_NR = FSI.FK_KSL_NR
JOIN KSF ON KSF.PK_KSF_NR = KSL.FK_KSF_NR
WHERE SUBSTR(KSF.PRUEFUNG_FACH,1,3) = 'Subj A'
AND SUBSTR(KSF.PRUEFUNG_FACH,1,3) = 'Subj B'
AND FST.IMMATRIKULIERT_JN = 'J'
AND KSL.TEXT_SCHEIN IS NOT NULL

But that gave me zero results. Of course, because for each finished subject it is a new row/tuple...
So I tried it like that:

SELECT FPS.NACHNAME, FPS.VORNAME, TO_NUMBER(KLIN_SEM) AS KLIN_SEM FROM FPS
JOIN FST ON FPS.PK_FPS_NR = FST.AK_FPS_NR
JOIN FSL ON FPS.PK_FPS_NR = FSL.FK_FPS_NR_STUDENT
JOIN FSI ON FSI.PK_FSI_NR = FSL.FK_FSI_NR
JOIN KSL ON KSL.PK_KSL_NR = FSI.FK_KSL_NR
JOIN KSF ON KSF.PK_KSF_NR = KSL.FK_KSF_NR
WHERE SUBSTR(KSF.PRUEFUNG_FACH,1,6) = 'Subj A'
AND FST.IMMATRIKULIERT_JN = 'J'
AND KSL.TEXT_SCHEIN IS NOT NULL

INTERSECT

SELECT FPS.NACHNAME, FPS.VORNAME, TO_NUMBER(KLIN_SEM) AS KLIN_SEM FROM FPS
JOIN FST ON FPS.PK_FPS_NR = FST.AK_FPS_NR
JOIN FSL ON FPS.PK_FPS_NR = FSL.FK_FPS_NR_STUDENT
JOIN FSI ON FSI.PK_FSI_NR = FSL.FK_FSI_NR
JOIN KSL ON KSL.PK_KSL_NR = FSI.FK_KSL_NR
JOIN KSF ON KSF.PK_KSF_NR = KSL.FK_KSF_NR
WHERE SUBSTR(KSF.PRUEFUNG_FACH,1,6) = 'Subj B'
AND FST.IMMATRIKULIERT_JN = 'J'
AND KSL.TEXT_SCHEIN IS NOT NULL

ORDER BY 3

And that was looking very good. But when I added more subjects via further INTERSECTS I found out that it was not working for unknown reasons, the resultset did not change compared to my query with only one INTERSECT, also with brackets.

What did I do wrong, and what would be the best way to get the data I want to get?

Thank You very much in advance!

With kind regards,
Chriss

Edited by: user9355711 on 17.03.2010 09:11