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…
I just spent the last five hours trying to move a Microsoft CRM (version 3) SQL/Reporting Server to a new piece of hardware. Despite having good documentation, it was an incredible chore, and in the end I still ended up having to abort the migration and go back to the old server.
It was really frustrating, because I was 95% of the way there—everything was working except the Reports, and they only weren’t visible in the CRM application (if you called them directly, they worked fine. I suspect it has something to do with permissions/trusts between the servers, but I couldn’t find it tonight. Plus, I’m just not very smart after working for 14+ hours straight.