Search code examples
mysqlsubquerywhere-clause

When I nest a query within another query I am getting the error "SQL error 1241 operand should contain 5 column(s)"

SELECT *
FROM AUTHOR
WHERE FLOOR(DATEDIFF(bDate,CURDATE())/365.25*-1) > 30 AND
    (SELECT a.*
    FROM AUTHOR a 
    WHERE (SELECT count(paperId) from AUTHOR_PAPER ap where ap.authorId = a.aEmail)) > 3

Both these queries work on their own, I just want to be able to find out both where clauses

Tables

author
(aEmail*
,fName
,lName
,bDate
,city
)

reviewer
(rEmail*
,phoneNumber
,lName
,fName
,city
)

paper
(paperId*
,title
,abstract
,submissionDate
)

author_paper
(authorId*
,paperId*
,isContact
)

paper_review
(paperId*
,reviewerId*
,score 
,reviewSubmissionDate
,reviewInvitationDate
)

* = (component of) PRIMARY KEY

Error im getting is : 17:05:14 SELECT * FROM AUTHOR WHERE FLOOR(DATEDIFF(bDate,CURDATE())/365.25-1) > 30 AND (SELECT a. FROM AUTHOR a WHERE (SELECT count(*) from AUTHOR_PAPER ap where ap.authorId = aEmail)) > 3 LIMIT 0, 1000 Error Code: 1241. Operand should contain 5 column(s) 0.000 sec

aEmail = authorId

Solution

  • It looks like you want authors that are older than 30 and that produced more than 3 papers. If so, I would recommend:

    select *
from author a
where 
    bdate <= current_date - interval 31 year
    and (select count(*) from author_paper ap where ap.authorid = a.email) > 3