CODEWITHSUNDEEP
sqlitejoinandroid-sqlite
VSB
VSB

Reputation: 10375

How to join one-to-many tables?

I have a scenario like below in SQLite: Some items which their prices during time is stored inside price table. Current prices for an item is the one which:

  1. Its Price.fkItemID == Item.id
  2. It is the most recent match (has the maximum ID among those who match in criteria 1)

So how can I join below tables in SQLite such that I get a list of items with their current prices?

Besides, having several joins, will it cause too much performance degrade? enter image description here

Upvotes: 0

Views: 339

Answers (1)

CL.
CL.

Reputation: 180060

To get the row with the largest ID, use a correlated subquery which orders by the ID, and take just the first returned row:

SELECT name,
       (SELECT price
        FROM Table2
        WHERE fkItemID = Table1.id
        ORDER BY id DESC
        LIMIT 1
       ) AS current_price
FROM Table1

This query is efficient if there is an index that allows searching on fkItemID and then sorting on id:

CREATE INDEX Table2_fkItemID_id ON Table2(fkItemID, id);

(If there aren't many historical prices, it is not really necessary to index the id column.)

Upvotes: 2

Related Questions