Error in WHERE clause

Question

1.00/5 (2 votes)

See more:

Hi, i am doing a sql question, n i typed out a code, it shows an error which is related to the Where Clause and subqueries. Pls someone give me a solution.

What I have tried:

SELECT B.movie_title AS 'Movie Title',
A.branchID AS 'Branch ID',
C.dvdID AS 'Total_Number_of_DVD'
FROM RENT A
JOIN MOVIE B ON(B.movieID = A.movieID)
JOIN DVD C ON(C.dvdID = A.dvdID)
WHERE A.dvdID IN (SELECT COUNT(A.dvdID) AS 'Total_DVDs_Rented' 
WHERE date_returned = NULL) AND A.dvdID IN (SELECT COUNT(C.dvdID - A.dvdID ) AS 'Total DVDs Available' 
WHERE date_returned = NULL) 
AND movie_title = 'DEADPOOL2';

Posted 28-Jun-18 2:12am

Member 13864666

Updated 28-Jun-18 21:26pm

Add a Solution

Comments

CHill60 28-Jun-18 8:22am

I assume the error is

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

?

CHill60 28-Jun-18 8:25am

You are mixing up IDs with COUNT of things - just because two things are a number doesn't mean you should be comparing them with each other.
Show some sample data and explain what you want as your expected results

W∴ Balboos, GHB 28-Jun-18 8:38am

Why do you has AS to name the return field in subqueries?

It would, of course, be useful, too, if you:
1 - gave us the actual error message your received
2 - respected us enough to actually write out words and not use texting garbage abbreviations.

#realJSOP 28-Jun-18 9:28am

This

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Answer 1 · 2018-06-28T03:35:00

Try it something this:

SQL

SELECT  B.movie_title AS [Movie Title],
        A.branchID    AS [Branch ID],
        C.dvdID       AS [Total_Number_of_DVD]
FROM    RENT AS A
INNER JOIN MOVIE AS B ON B.movieID = A.movieID
INNER JOIN DVD   AS C ON C.dvdID = A.dvdID
WHERE A.dvdID IN (SELECT A.dvdID from RENT)
and   date_returned = NULL 
AND   movie_title = 'DEADPOOL2';

CAVEAT: I have no idea if this is gonna work because I don't have schemas or data to work with, so I leave it to you - as a sql programmer - to massage it to fit your needs.

amit kalshetti · Answer 2 · 2018-06-28T21:27:00

Solution 2

use only one where clause and for another condition use and / or clause not where...

Only One where clause we can use otherwise give syntax error.

Posted 28-Jun-18 21:27pm

amit kalshetti

Comments

Richard Deeming 29-Jun-18 10:13am

It's hard to see due to the lack of indentation, but the additional WHERE clauses in the question are within sub-queries. The syntax of that part of the query is valid.