INSERT INTO M (CombinedKeyField, EvtNbr)
VALUES ('A', 1),
('A', 5);
INSERT INTO E (CombineKeyField, EvtNbr)
VALUES ('A', 1)
, ('A', 2)
, ('A', 3)
, ('A', 4)
, ('A', 5)
, ('A', 6)
, ('A', 7)
, ('A', 8)
, ('A', 9)
, ('B', 1);
I'm ignoring the TransDate part for now.
This is what I want:
SELECT E.CombinedKeyField, E.EvtNbr, M.EvtNbr;
A 1 1
A 2 1
A 3 1
A 4 1
A 5 5 -- matches the (A,5) record in the M table.
A 6 1
A 7 1
A 8 1
A 9 1
B 1 NULL -- no match found for CombinedKeyfield in M
This is the closest I have got
select E.CombinedKeyField, E.EvtNbr, M.EvtNbr
from E left join M
on E.CombinedKeyField = M.CombinedKeyField
and (E.EvtNbr = M.EvtNbr
or M.EvtNbr = (SELECT MIN(M1.EvtNbr) FROM M M1
WHERE M1.CombinedKeyField = E.CombinedKeyField
)
)
But it doubles up on A,5, matching both on A,1 and A,5 in M
And it doesn't return B,1 with no match even though it is a left join.
Hopefully that comes out readable, and my needs are clearer.
Thanks,
David
----- Original Message -----
From: James K. Lowden <[email protected]>
To: [email protected]
Sent: Monday, September 19, 2016 10:57 AM
Subject: Re: [sqlite] Complicated join
On Thu, 15 Sep 2016 15:53:10 +0000 (UTC)
David Bicking <[email protected]> wrote:
> (1) The CombinedKeyFields must always match in each table(2) Match
> using the EvtNbr, but if no match, use the lowest M.EvtNbr that
> matches the CombinedKeyFields
>
> (3) Match using the TransDate but if no exact match, match on the
> M.TransDate that is less than the E.TransDate but greater than the
> prior E.TransDate
I think this is what you describe:
select E.CombinedKeyFields, max(M.EvtNbr) as EvtNbr, max(M.TransDate)
as TransDate from E left join M
on E.CombinedKeyFields = M.CombinedKeyFields
and E.EvtNbr >= M.EvtNbr
and E.TransDate >= M.TransDate
--jkl
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
