CODEWITHSUNDEEP
sqlite
Elye
Elye

Reputation: 60151

SQLite: How to combine SQL of order limit with left join queries

I have a record of A, B, C and D.

My SQL1 SELECT * FROM main_table order by main_table.date desc limit 2 returns A and B.

My SQL2 SELECT * FROM main_table left join sub_table using (key) where sub_table._id is not null returns B and C.

I want to have a single SQL Statement that returns A, B and C. Basically, I want to join SQL1 or SQL2

How could I combine this two SQLs (in SQLite) optimally?

My data is as below

 main_table
_id  date        key
1    2016-08-04  D
2    2016-10-06  A
3    2016-09-04  B
4    2016-07-05  C

sub_table
_id  age  key
1    8    B
2    9    C

Desire Output

m._id  m.date     m.key  s._id s.age s.key
2     2016-10-06  A
3     2016-09-04  B      1     8     B
4     2016-07-05  C      2     9     C

My logic of selection... I want to pick the top two latest data, and also data that has an age. The others I don't care (i.e. the D is not in top 2, and doesn't have an age).

Upvotes: 1

Views: 41

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

If I read correctly, then a UNION might be what you have in mind:

SELECT * FROM
   (SELECT * FROM main_table LEFT JOIN sub_table USING (key)
    ORDER BY date DESC LIMIT 2)
UNION
    SELECT * FROM main_table LEFT JOIN sub_table USING (key)
    WHERE sub_table._id IS NOT NULL

Upvotes: 1

Related Questions