Possible Subquery Help 1

[anthonymeluso]

I have two tables. I called AlumniList and the other DonationList. I need to run a report that shows me that someone hasn't donated this year when they had in the past. The query below does not return any results. Does anyone have any suggestions?

SELECT Donations.DonationDate, AlumniList.FirstName, AlumniList.LastName, AlumniList.Class, Donations.Donation
FROM AlumniList INNER JOIN
Donations ON AlumniList.AlumniID = Donations.AlumniID
WHERE (Donations.DonationDate < '2005/12/31 11:59:00 PM')and AlumniList.AlumniID Not In
(select Donations.AlumniID
From Donations
Where (Donations.DonationDate > '2006/01/01 12:00:00 AM'))

Thanks!

Anthony

 

[vbkris]

yeah, will check this up and come back to u dude...

Known is handfull, Unknown is worldfull

 

[anthonymeluso]

Thanks. I'm giving you a star for just hanging on for this long!

Anthony

 

[vbkris]

Known is handfull, Unknown is worldfull

 

[vbkris]

hi,

it works fine for me, here is the design that i have:

Alumni Table
------------
A_ID
A_NAME



Donation Table
--------------
D_ID
A_ID
F_ID
D_SUM
D_YEAR

Friend Table
------------
F_ID
F_NAME


Data:

Alumni Table
-------------
1 X
2 Y

Friend Table
-------------
1 A
2 B

Donation Table
--------------
1 1 1 1000 2000
2 2 1 1100 2000
3 2 2 2000 2001


The query that i used:

SELECT DonationTable.*
FROM DonationTable INNER JOIN
AlumniTable ON DonationTable.A_ID = AlumniTable.A_ID AND DonationTable.D_DATE = 2000
WHERE (DonationTable.A_ID NOT IN
(SELECT A_ID
FROM DonationTable DonationTable1
WHERE D_DATE = 2001))


This gave me the correct answer!!!

Known is handfull, Unknown is worldfull

 

[anthonymeluso]

What if the Friend Id is null? So it would look like this.

Cause who would a friend and an alumni donate on the same row?
It can only be one or the other.

Donation Table
----------

1 1 Null 1100 2000

What will that give you?

Thanks,

Anthony

 

[vbkris]

will check that too dude and let u know...

Known is handfull, Unknown is worldfull

 

[anthonymeluso]

Hey just checking to see if it worked?


Thanks,

Anthony

 

[vbkris]

yeah dude, it worked

Donation Table
--------------
1 1 NULL 1000 2000
2 2 NULL 1100 2000
3 2 2 2000 2001


Known is handfull, Unknown is worldfull