How to write an SQL SELECT that returns rows that do not get returned from my current SELECT

Question

1.00/5 (1 vote)

See more:

string query = "select ad.student_id,ad.full_name from add_student ad inner join billing b on ad.student_id = b.student_id where month (b.fee_of_month) = month(#"+DateTime.Now.ToShortDateString()+"#) and year(b.fee_of_month)= year(#"+DateTime.Now.ToShortDateString()+"#)";

I am using above query to retrieve record of those students that have paid fees for this month. This query is working fine.

I want a new query to retrieve rows for those students who have not paid their fees.

If I use query like below, then it will not return rows as expected. Please suggest a query that I can use to achieve my task. Thank you.

string query = "select ad.student_id,ad.full_name from add_student ad inner join billing b on ad.student_id = b.student_id where month (b.fee_of_month) <> month(#"+DateTime.Now.ToShortDateString()+"#) and year(b.fee_of_month)= year(#"+DateTime.Now.ToShortDateString()+"#)";

Posted 5-Mar-13 5:36am

Harpreet_125

Updated 5-Mar-13 7:12am

Mike Meinz

v5

Add a Solution

Comments

Richard C Bishop 5-Mar-13 11:38am

You will have to change your WHERE clause to check for a column related to the fees.

Sergey Alexandrovich Kryukov 5-Mar-13 11:40am

Just a warning: even though it makes sense, formally it's not a question. Please, try to make all "questions" on this forum shaped as distinct questions.

This is the reason: you want it because the forum is overwhelmed with non-questions these days. You don't want your post to be accidentally removed or reported for abuse...

—SA

Harpreet_125 5-Mar-13 11:49am

can you please suggest me query..

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)

recoverinfotech · Answer 1 · 2013-03-05T13:16:00

use

SQL

SELECT * FROM add_student ad inner join billing b on ad.student_id = b.student_id HAVING  (select ad.student_id,ad.full_name from add_student ad inner join billing b on ad.student_id = b.student_id where month (b.fee_of_month) = month(#"+DateTime.Now.ToShortDateString()+"#) and year(b.fee_of_month)= year(#"+DateTime.Now.ToShortDateString()+"#))

Mike Meinz · Answer 2 · 2013-03-05T05:47:00

Your new query will look something like this. Sorry, I do not have time to build a test case to test it for you. The concept is that you select from billing those student_ids that have paid and from add_student you select those student_ids that are not in the set of those that have paid. I hope this helps you.

string query = "select ad.student_id,ad.full_name from add_student ad where ad.student_id not in (select b.student_id from billing b where month (b.fee_of_month) = month(#"+DateTime.Now.ToShortDateString()+"#) and year(b.fee_of_month)= year(#"+DateTime.Now.ToShortDateString()+"#))";

How to write an SQL SELECT that returns rows that do not get returned from my current SELECT

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0