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]

currently this is what i can give:

select * from alumni where alumni id in (select alumniid from donation where year=2000) and
alumni not id in (select alumniid from donation where year=2001)


the data is sample data (like the year data). will get back to u if there is a simpler option...

Known is handfull, Unknown is worldfull

 

[vbkris]

try this too:

select AlumniTable.Id,Name from
AlumniTable
inner join
(select PrevYearTable.Id from
(
select Id from donation where Year=2000
) PrevYearTable
Left Join
(
select Id from donation where Year=2001
) CurrentYearTable
on
PrevYearTable.Id=CurrentYearTable.Id
where
CurrentYearTable.Id is NULL
) OutputTable
on
AlumniTable.id=OutputTable.id


Known is handfull, Unknown is worldfull

 

[anthonymeluso]

I'm having trouble understanding your second post. I tired your suggestion in the first post and it didn't work. Its so easy to find new donors why should it be hard to find donors who gave in the past but not this year.

SELECT donationlist.AlumniID,DonationList.DonationDate,AlumniList.Class, AlumniList.FirstName, AlumniList.LastName, DonationList.Donation
FROM DonationList Inner JOIN
AlumniList ON DonationList.AlumniID = AlumniList.AlumniID
WHERE DonationList.AlumniID IN
(Select DonationList.AlumniID
From DonationList
Where DonationList.DonationDate < CONVERT(SMALLDATETIME, '2005-12-31 00:00:00', 102) ) And DonationList.AlumniID Not In
(Select DonationList.AlumniID
From DonationList
Where DonationList.DonationDate > CONVERT(SMALLDATETIME, '2006-01-01 00:00:00', 102) )

I kinda feel you on the Left Join though. Any other suggestions would be great.

Thanks,

Anthony

 

[vbkris]

ur first query itself is correct. with that if my query(first one) is also not working then i suggest that u check up the data too.

explanation to my second query:

select AlumniTable.Id,Name from
AlumniTable
inner join
(
select PrevYearTable.Id from
(
select Id from donation where Year=2000
)
PrevYearTable
Left Join
(
select Id from donation where Year=2001
)
CurrentYearTable
on
PrevYearTable.Id=CurrentYearTable.Id
where
CurrentYearTable.Id is NULL
)
OutputTable
on
AlumniTable.id=OutputTable.id


what i do is use derived tables.
this select query will give members who have donated for 2000
(
select Id from donation where Year=2000
)
PrevYearTable

this select query will give members who have donated for 2001
(
select Id from donation where Year=2001
)
CurrentYearTable


a left join of prevyear and currentyear where currentyear is null will give those alumni who have NOT paid for this year but have paid in the past.

the above output is enclosesd in a table called OutputTable.

this is inner joined with ur alumni table. i wrote the same logic in bits and pieces to get a better idea...

Known is handfull, Unknown is worldfull

 

[anthonymeluso]

I hate myself. I figured it out. My subquery needed a inner join.

SELECT AlumniList.AlumniID, AlumniList.Class, AlumniList.LastName, DonationList.Donation, DonationList.DonationDate
FROM DonationList INNER JOIN
AlumniList ON DonationList.AlumniID = AlumniList.AlumniID
Where donationlist.donationdate < CONVERT(SMALLDATETIME, '2006-01-01 00:00:00', 102) and donationlist.alumniid not in
(select donationlist.alumniid
FROM DonationList INNER JOIN
AlumniList ON DonationList.AlumniID = AlumniList.AlumniID
where donationlist.donationdate > CONVERT(SMALLDATETIME, '2006-01-01 00:00:00', 102))

It works like a charm now. Thanks for the guidence.

Anthony

 

[vbkris]

why does it need an inner join? i dont get it! the sub query does not need an inner join!

why?
cause NOT IN will take care of that...

Known is handfull, Unknown is worldfull

 

[anthonymeluso]

It only works with a inner join. Is this just bad Database design in here somewhere? I'm pretty sure my relationships are correct.

Thanks though,

Anthony

 

[vbkris]

hmm, could u provide some sample data for me from the 2 tables???

Known is handfull, Unknown is worldfull

 

[anthonymeluso]

From the alumniList Table

39 1959 M NULL Mauricio De Bedout
NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL NULL NULL NULL
NULL NULL NULL 46 NULL False False NULL NULL
2/8/2006 10:24:00 AM True False True False NULL

From the donationlist table

39 3 554.0000 39 NULL NULL NULL False
4/5/1999 4:21:00 PM False 4/7/2006 12:25:00 AM

I'm not going to lie to you here. I build this same DB at home with sql express and it worked without that inner join.

Thanks

Anthony

 

[vbkris]

dude, will check and let u know...

Known is handfull, Unknown is worldfull

 

[anthonymeluso]

Hey would this matter if there are columns that other than alumniID. Such as businessID and friendsID. These people can also donate and have their foreign keys in donationlist. Maybe that is why I need that inner join???

Just a guess?

Anthony

 

[vbkris]

nope, cause u have clearly specified in ur join that only alumni id has to be joined in the donations table, not the alumni's friend's id...

Known is handfull, Unknown is worldfull

 

[anthonymeluso]

Ok get this I think it does. On the one I have working at home which worked perfectly with your query; I just added a column to the donation table called friendID which is linked to a newly created table called friend list.

Now when I run your query it shows nothing. When I update the subquery with a inner join again on alumniID it works!

So that diffinitly has something to do with it. I know I specified to do a inner join on the outer query but the sub query needs it again to pull the right rows. At lest that is what I think is happening. Try it yourself in a fake table.

First only have an alumniID column and test your original query against it. Then create a table called friendlist and add a column to the donation table and run the same query nothing will show up.

Try it!

Anthony

 

[vbkris]

sure, will try tat dude...

Known is handfull, Unknown is worldfull

 

[anthonymeluso]

As the plot thickens. So I go ahead and undo my changes and for some reason I can't get your query to work again against my home DB. I really don't know at this point.

 

[anthonymeluso]

Hey did you every try that test DB? Was wondering if it worked?

Thanks,

Anthony

 

[vbkris]

nope, will try to do it tonight...

Known is handfull, Unknown is worldfull

 

[vbkris]

hi,

didnt get the one regarding:
>>Then create a table called friendlist and add a column to the donation table and run the same query nothing will show up

how exactly is the design, currently this is how i have it:

AlumniTable
---------
A_ID
A_NAME

DonationTable
-------------
D_ID
A_ID
D_DATE


where exactly does the friend table fit here???

Known is handfull, Unknown is worldfull

 

[anthonymeluso]

Its another table. That contains people associated with the school that donate but are not alumni. So the DonationTable should look like this.

DonationTable
----------
D_ID
A_ID
D_Date
FriendID
Donation

FriendTable
-----------
FriendID
FriendName

Now I know this probably is a bad idea. However I don't know about having a seperate table for each type of person that can give. I just one table to handle all our donations, no matter how it comes from.

Do you get it know?

Thanks,
Anthony