CODEWITHSUNDEEP
sqloracle-database
Mark Brealey
Mark Brealey

Reputation: 13

SQL Statement issues (Not IN)

Working through a uni assignment (down to the deadline), Can't figure out how/why im mis-using the NOT IN function.

SELECT DISTINCT P1.TITLE, P1.PANUM, D1.POSTCODE
FROM PAPER P1
INNER JOIN AUTHOR A1 ON A1.PANUM = P1.PANUM
INNER JOIN ACADEMIC A2 ON A2.ACNUM = A1.ACNUM
INNER JOIN DEPARTMENT D1 ON D1.DEPTNUM = A2.DEPTNUM
WHERE P1.PANUM NOT IN(
    SELECT DISTINCT P1.TITLE, P1.PANUM, D1.POSTCODE
    FROM PAPER P1
    INNER JOIN AUTHOR A1 ON A1.PANUM = P1.PANUM
    INNER JOIN ACADEMIC A2 ON A2.ACNUM = A1.ACNUM
    INNER JOIN DEPARTMENT D1 ON D1.DEPTNUM = A2.DEPTNUM
    WHERE D1.POSTCODE LIKE '4%'
)
ORDER BY P1.PANUM;

The intention is to create a list of P1.TITLE that has no authors from a postcode starting in 4.

I'm getting a ORA-00913: Too Many Values error, and can't quite wrap my head around why.

Upvotes: 1

Views: 49

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272106

Just focusing on your problem:

The intention is to create a list of P1.TITLE that has no authors from a postcode starting in 4.

You can simply do this:

SELECT TITLE, PANUM
FROM PAPER
WHERE NOT EXISTS (
    SELECT 1
    FROM AUTHOR
    INNER JOIN ACADEMIC ON AUTHOR.ACNUM = ACADEMIC.ACNUM
    INNER JOIN DEPARTMENT ON ACADEMIC.DEPTNUM = DEPARTMENT.DEPTNUM
    WHERE AUTHOR.PANUM = PAPER.PANUM
    AND DEPARTMENT.POSTCODE LIKE '4%'
)
ORDER BY PANUM

Upvotes: 1

Nakul
Nakul

Reputation: 137

When you use IN and NOT IN keyword, that means only one parameters (or column) should be inside it.

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

you have to select one column on inner subquery

SELECT DISTINCT P1.TITLE, P1.PANUM, D1.POSTCODE
FROM PAPER P1
INNER JOIN AUTHOR A1 ON A1.PANUM = P1.PANUM
INNER JOIN ACADEMIC A2 ON A2.ACNUM = A1.ACNUM
INNER JOIN DEPARTMENT D1 ON D1.DEPTNUM = A2.DEPTNUM
WHERE P1.PANUM NOT IN(
    SELECT  P1.PANUM
    FROM PAPER P1
    INNER JOIN AUTHOR A1 ON A1.PANUM = P1.PANUM
    INNER JOIN ACADEMIC A2 ON A2.ACNUM = A1.ACNUM
    INNER JOIN DEPARTMENT D1 ON D1.DEPTNUM = A2.DEPTNUM
    WHERE D1.POSTCODE LIKE '4%'
)
ORDER BY P1.PANUM;

Upvotes: 0

Related Questions