[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] Complicated join
From:       Luuk <luuk34 () gmail ! com>
Date:       2016-09-20 18:53:04
Message-ID: 0aab0f53-6ad4-599b-72ed-760253a98fff () gmail ! com
[Download RAW message or body]



On 19-09-16 21:28, David Bicking wrote:
> 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
> 
> Did this part of my post not make it to your reader?
> Your output is almost what I want, except that the A.5 line is matching 1 and 5 in \
> the M table, and I only want it to match the 5. 
> Now, can you suggest how I can get the query to return A,5,5 but not A,5,1?
> Thanks,David

sqlite> select E.CombinedKeyField, E.EvtNbr, max(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
    ...>                )
    ...> ) GROUP BY E.CombinedKeyField, E.EvtNbr ;
A|1|1
A|2|1
A|3|1
A|4|1
A|5|5
A|6|1
A|7|1
A|8|1
A|9|1
B|1|
sqlite>


_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[prev in list] [next in list] [prev in thread] [next in thread]