INNER JOIN or nested SELECT in this case?

Question

I have two tables:

atvtrails_prim:

ID int PK
ID2 int
TType varchar(100)

atvtrails_sec:

ID2 int Foreign Key(Relates to ID2 of atvtrails_prim)
Latitude double
Longitude double

What I want from these tables: All the records in atvtrails_sec whose ID2 has a specific TType in atvtrails_prim.

Query I am using(Nested SELECT):

SELECT atvtrails_sec.ID2,atvtrails_sec.Latitude, atvtrails_sec.Longitude,atvtrails_prim.TType 
FROM atvtrails_sec,atvtrails_prim 
WHERE atvtrails_sec.Latitude BETWEEN %@ and %@ 
AND atvtrails_sec.Longitude BETWEEN %@ and %@ 
AND (
    SELECT TType from atvtrails_prim where ID2=atvtrails_sec.ID2
    ) =%@", lrLat, ulLat, lrLong, ulLong,type2

lrLat, ulLat, lrLong, ulLong,type2 are variables. I am coding in objective-c.

This query gives me a very large dataset. It does give me the correct ones but they are being duplicated. I get around 90000 results while the table has only 18000 records. This query is taking a lot of time when I measured and I want a faster solution.

Query I am using(INNER JOIN):

SELECT atvtrails_sec.ID2,atvtrails_sec.Latitude, atvtrails_sec.Longitude 
FROM atvtrails_sec 
INNER JOIN atvtrails_prim ON atvtrails_prim.TType=%@ 
WHERE atvtrails_sec.Latitude BETWEEN %@ and %@ 
AND atvtrails_sec.Longitude BETWEEN %@ and %@", lrLat, ulLat, lrLong, ulLong,type1

This is not returning me any data. I am new to SQLite. Kindly guide me as to how can I get the desired data using Inner-Join. Also, is there any way to optimize my nested SELECT query so that it doesn't give me extra data?

You can highlight code segments in your question and click the {} icon to preserve code formatting. Also try to format the code in a readable way. Finally, are you using mysql or sqlite? Delete the improper tag. — Hart CO
Having trouble parsing your queries, but it doesn't look like you're ever actually joining your tables together in your second query. There are tons of tutorials out there, here's one: Clicky — Andrew

oliakaoil oliakaoil · Accepted Answer · 2014-07-01T15:25:47

Would something like this work?:

SELECT prim.ID,sec.*
FROM atvtrails_prim prim
LEFT JOIN atvtrails_sec sec ON sec.ID2 = prim.ID2
WHERE prim.TType = 'dirt';

Also, you might want to index your atvtrails_prim.TType column for better query performance:

ALTER TABLE atvtrails_prim ADD KEY TType (TType );

INNER JOIN or nested SELECT in this case?

2 Answers