Re: SubQuery Help
oops. probaly should clairfy
yes, I meant to say contains rather than starts with...simple error on my part
but as far as the dates go
this started out in that next to each customer name in the result set I not only want to display the name, address, city, etc, but the number of days since the last visit to that customer. And I also want to be able to sort by the number of days since the last visit.
So since I have to reference the number of days since the last visit twice in the query (once as a result column, once as an ORDER BY value), I was looking for a way to "store" the data as an alias so I could use it more than once in the query statement (once again, once to include it as a result column, and a second time as an ORDER BY value)
So imagine we have a table called "contacts"
RecordID, LastName
10, Smith
20, Jones
30, Johnson
40, Munster
And we have a table called "calendarevents"
RecordID, CustomerLink, EventDate
1,30,2004-08-04
2,30,2004-08-01
3,20,2004-07-08
So I want to do a search on customers in the contacts database and get something like this
Customers with the letter J in there name sorted by Number of Days Since there last Visit
Customer Name ------------------ Days Since Last Visit
===================================
Johnson 11 Days
Jones 35 Days
It sounds like I can't do this with a single query? I was just figuring that I just didn't understand complex SQL enough to make this happen, but then again, perhaps it can't be done as elegantly as I would like?