I Just Don’t Get It
Posted in Code
First dog watch, 1 bell (4:51 pm)

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…

Leave a Comment »

Leave a Reply