join two tables and return best “not overlapping” matches

Question

By not overlapping matches, I mean that each row should be returned at most once. That seems to be the hard part.

I managed to get the best not overlapping matches, using the following query where pair is a view that has all possible matches as (id1,id2,val1,val2) rows.

SELECT p.* FROM pair p
    LEFT JOIN pair p1 ON p1.id1 = p.id1 AND p1.val2 < p.val2
    LEFT JOIN pair p2 ON p2.id2 = p.id2 AND p2.val1 < p.val1
WHERE
    p1.id1 IS NULL
    AND p2.id2 IS NULL;

Full sqlfiddle here http://sqlfiddle.com/#!9/68614/2

For values a,b in t1 and a,d in t2 I want it to return pairs (a,a) and (b,d) but it only returns (a,a)

Could someone provide a working solution? Or if this kind of matching would fundamentally be better done on the client, could you explain why?

-- EDIT

The problem I'm trying to solve is similar to the one discussed here: Retrieving the last record in each group

My requirements are higher, I need in addition that matches don't overlap.

Please edit your question with sample data and desired results. I, for one, do not understand what you mean by "the best not overlapping matches". — Gordon Linoff, Jul 10 '15 at 11:00
Clearly it is not clear, or I would not have added the comment. — Gordon Linoff, Jul 10 '15 at 11:02
I've spent a lot of time simplifying and provided sample data in an sqlfiddle, don't know what more to do... maybe more concrete data... i'll try to... meanwhile, maybe someone who has already encountered this kind of matching problem could answer the question — klarezz, Jul 10 '15 at 11:07
@klarezz . . . The issue isn't the data. The issue is what you are looking for. What results do you want? (You should put both the sample data and desired results in the question.) — Gordon Linoff, Jul 10 '15 at 11:31

Stanislovas Kalašnikovas · Answer 1 · 2015-07-10T11:09:30.780

0

Are you looking for something like that?

SELECT p.* FROM pair p
    LEFT JOIN pair p1 ON p1.id1 = p.id1 AND p1.val2 < p.val2
    LEFT JOIN pair p2 ON p2.id2 = p.id2 AND p2.val1 < p.val1
WHERE
    p.id1 = p.id2
ORDER BY id1

SQL FIDDLE

edited Jul 10 '15 at 11:09

answered Jul 10 '15 at 11:04

Stanislovas Kalašnikovas

9,203
4
32
59

thanks for the suggestion, but id can't be used for matching, it can be anything. I have used ids 1 and 2 as an example. – klarezz Jul 10 '15 at 11:13
2

So describle clearly what you need, provide sample data and desired results. – Stanislovas Kalašnikovas Jul 10 '15 at 11:20

join two tables and return best “not overlapping” matches

1 Answers1