tough query: select where unique to one subset

it's probably simpler than I'm making it ...

[jcmoon]

I have a table with a dozen columns, and then a date column.

For two given dates, I want to select all entries which occur on one date, and not on the second.

So for example, say the table is a log of visitors, and the fields are date, url, browser, ip. What I want is to select the specific url-browser-ip combinations that occur on the first date, and don't occur on the second date.

And I get lost there. Am I just making it too complicated? Is there a simpler way to describe what I'm trying to do?

Possible solutions I've gone after:
- somehow treat an entry of multiple columns as a single field, and then count the dates for which this occurs, and select those where count=1 and the date is the given one. Didn't work.
- select the group HAVING COUNT(date)=1 ... this query did run, but this returned an empty set.

[jcmoon]

I did have another idea for this: select the entire table of entries for the given day into Perl array, and then foreach element in the array, see if that entry occurs on the other day. Problem? Each date in this table has 45,000+ entries. No, there's got to be a better way than that ...

[jcmoon]

Another try:

SELECT * FROM table WHERE date='date1' AND other,fields,comma,separated NOT IN (SELECT * FROM table WHERE date='date2')

This didn't work, either. Our MySQL is version 3.23, and I think subqueries aren't supported until version 4.1 anyway, so this was more of a hail-mary ...

[FalseDawn]

The following may work - table "test", cols mydate (varchar for illustration - will need to be changed for a datetime field), url, browser and ip)

SELECT * FROM test T1 LEFT JOIN (SELECT * FROM test WHERE
test.mydate='date2') T2 on T1.url=T2.url AND T1.browser=T2.browser AND T1.ip=T2.ip WHERE T1.mydate='date1' AND T2.mydate IS NULL

But you really should upgrade your mysql - that version is ancient.

[jcmoon]

Yeah, a newer version of MySQL would handle that beautifully, I'm sure. Until then, though, I fear a simple brute-force run through might be the only option, and I really don't want to do that ...