CODEWITHSUNDEEP
sqlalgorithmsqliteselectwhere-clause
Momergil
Momergil

Reputation: 2281

How to select items of a SQLite table based on a column with a specific propriety located in another table?

I have the following situation: there are 2 tables in my SQLite database one of which is called "assets" and the other "operations".

"Assets" include a list of market assets each of which having a specific "Asset type" value alongisde its name.

"Operations" have lots of columns one of which being "asset"; each operation is done with one single asset.

Now I have a table in my app I want to populate with "all operations related to a specific asset type", something that needs to be done quick. My question is: how can I do this in an both fast and elegant way?

Two ways I have now, but both of them are inadequate: one is simply to add a new row called "asset type" in the operations table making the search pretty straightforward. The problem is that this is far from elegant since there is no direct connection between operations and asset types. Another solution would be to first call for a list of assets of some type and then do the look in "operations" table only for "assets included on this list". But this would be a processing monster since there could be dozens to hundreds of assets per type.

Is there any other way I couldn't figure or find out?

Upvotes: 0

Views: 119

Answers (1)

CL.
CL.

Reputation: 180020

Just do it in the obvious way:

SELECT *
FROM Operations
WHERE AssetID IN (SELECT AssetID
                  FROM Assets
                  WHERE AssetType = ?);

The same could be done with a join

SELECT Operations.*
FROM Operations
JOIN Assets USING (AssetID)
WHERE AssetType = ?;

Upvotes: 1

Related Questions