Sqlite joining two tables

Question

first let me say sorry for not finding answer on the online forum, but I don't even know how to search for and answer to my question.

I have some basic SQL knowledge.

I have two tables and I am trying to make some sort of join. I tried left join, but that doesn't give the correct results. I also tried with operator <= in "left join B on A.Id <= B.A_ID" but that didn't work either.

Table A
ID A_val1 A_val2...
1   x1     y1
2   x2     y2
3   x3     y3
.
.
8   x8     y8
9   x9     y9

Table B
ID A_ID B_Val
1   1     0
2   5     1
3   8     2
4   9     3

Results that I need: last 4 rows in Table A joined with table B

A_ID A_Val1 A_val2 B_val
6   x6     y6      1
7   x7     y7      1
8   x8     y8      2
9   x9     y9      3

Best regards, Greg

Answer 1

Maybe something like:

select     a.ID
          ,a.A_Val1
          ,a.A_val2
          ,b.B_val
from      a
left      join b in a.ID = b.A_ID
Where     a.ID > 5

-- OR

select    a.ID
          ,a.A_Val1
          ,a.A_val2
          ,b.B_val
from      a
left join b in a.ID = b.A_ID
Order by  a.ID desc
LIMIT     4

Hope this helps.

Answer 2

You could use a join with a LIMIT query:

SELECT
    a.ID AS A_ID,
    a.A_Val1,
    a.A_val2,
    b.B_val
FROM
(
    SELECT ID, A_val1, A_val2
    FROM TableA
    ORDER BY ID DESC
    LIMIT 4
) a
INNER JOIN TableB b
    ON a.ID = b.A_ID
ORDER BY
    a.ID;

Demo