I’m trying to wrap my head around a nasty MS-SQL one-to-many JOIN problem and I just can’t get it to work out.
Basically, I have table a and table b, where a.index = b.index. b can have multiple entries per a.index, but I only care about the first match.
What I want to do is get something like this:
SELECT a.field1, a.field2, b.field
FROM a
JOIN b ON a.index = b.index
WHERE a.field1='this'
...etc...
But for every additional entry in b, I get another row for a. The only field guaranteed to be unique in b is a uniqueid field. It’s hard to explain because the whole thing is a complicated mess.
Basically, a is a table of invoices, and b is a table of products added to the invoice. Most of the time, we have one product per invoice, but not always. I only care about seeing the first match. I thought a MIN() or MAX() would get me where I need to be, but it doesn’t work—the only thing that’s ever different in b is the uniqueid.
It’d be great if you could just JOIN TOP 1 in MSSQL but you can’t. Any ideas?
9:05pm Update: I just had a thought about this: how about JOINing to a view that is just a simple SELECT DISTINCT a.uniqueid FROM b? I’ll have to try this out tomorrow…
Google Reader just reversed their logic for reading RSS feeds—instead of the Mark as Read checkbox at the bottom of each item, now it says Keep Unread. The problem is unless you click on the text of the article somewhere, it never gets marked as read in the first place.
I hate stupid UI changes.