CODEWITHSUNDEEP
androidsqlite
Frink
Frink

Reputation: 221

Is there better way to select values(SQLite) using referent table

I have one referent table with codes for cars, (cars make,cars model,car kind,etc) in this table i have codes. For example:

+--------+--------+--------+--------+
|cr_code |cr_make |cr_model|cr_kind |
+--------+--------+--------+--------+
|1       |22      |345     |10      |     
|2       |22      |341     |5       |  
|3       |26      |458     |8       |  
|4       |26      |459     |77      |  
|5       |26      |501     |11      |  
|6       |39      |552     |2       |  
+--------+--------+--------+--------+

I have tables car_make,car_model,car_kind to.

For example car_make:

+--------+--------+--------+
|cm_code |cm_id   |cm_name |
+--------+--------+--------+
|1       |22      |mercedes|           
|2       |26      |bmw     |        
|3       |39      |audi    |   
+--------+--------+--------+

For example car_model:

+--------+--------+--------+
|co_code |co_id   |co_name |
+--------+--------+--------+
|1       |458     |520I    |           
|2       |459     |520IA   |        
|3       |501     |523IA   |   
+--------+--------+--------+

Now i need get all car models for user chosen car make(if bmw only bmw models). One car make can have multiple models because of that I need use referent table to find id witch model i can to show. I use SQLite select on way:

SELECT * FROM CAR_MODEL WHERE CO_CODE IN (Select DISTINCT CR_MODEL FROM CARS WHERE CR_MAKE="26") ORDER BY CO_ID ASC

This work , and show ok results,but I am not sure is this best way?

Thanx

Upvotes: 2

Views: 75

Answers (1)

gorefest
gorefest

Reputation: 884

Notice, most databases create a subtable when using the IN clause which will discard all indexes made on the referent table. This can become a major performance problem on large data sets.

Dependening on the version of SQLite it support the INNER JOIN clause:

SELECT CM.* 
  FROM CAR_MODEL CM 
 INNER JOIN CAR_MAKE MK 
    ON CM.CO_CODE ON MK.CR_MODEL 
 WHERE MK.CM_NAME='BMW'

Btw, you should consider using the SQLITE id column for joining references.

For large datasets, also consider matching column indexes for gaining performance.

Upvotes: 1

Related Questions