Print

---

Ken,

If I understand the logic correctly, you need a list of institutions with dates after July 1, but only institutions that have records existing before July 1.  Subqueries could work, but a view might be easier to work with, especially if you plan to a lot of queries with similar logic.

First, create a view with institutions whose dates are prior to July 1:

CREATE VIEW my_list AS SELECT DISTINCT institution FROM renewals WHERE snap_date < '2011-07-01'

Then, match the institutions in your renewals table with the ones in the view:

SELECT institution FROM renewals a, my_list b WHERE a.institution = b.institution AND snap_date > '2011-07-01'

That's the way, at least, I might tackle it... there are probably a dozen others however.

...adam



On Sep 28, 2011, at 12:41 PM, Ken Irwin wrote:

> Hi all,
> 
> I've not done much with MySQL subqueries, and I'm trying right now with what I find to be surprising results. I wonder if someone can help me understand.
> 
> I have a pile of data that with columns for "institution" and "date". Institution gets repeated a lot, with many different dates. I want to select all the institutions that *only* have dates after July 1 and don't appear in the table before that. My solution was to do a first query for all the institutions that DO have dates before July 1
> SELECT distinct institution FROM `renewals` WHERE snap_date < '2011-07-01'
> 
> And then to do a SELECT query on all the institutions:
> SELECT distinct institution from renewals
> 
> And then try to do a NOT IN subquery subtracting the smaller query from the larger one:
> 
> SELECT distinct institution from renewals
> WHERE institution not in
> (SELECT distinct institution FROM `renewals` WHERE snap_date < '2011-07-01')
> 
> ...only it doesn't seem to work. Or rather, the query has been running for several minutes and never comes back with an answer. Each of these two queries takes just a few milliseconds to run on its own.
> 
> Can someone tell me (a) am I just formatting the query wrong, (b) do subqueries like this just take forever, and/or (c) is there a better way to do this? (I don't really understand about JOIN queries, but from what I can tell they are only for mixing the results of two different tables so I think they might not apply here.)
> 
> Any advice would be most welcome.
> 
> Thanks
> Ken