I have 4 tables: Table1, Table2, Table3, Table4 each one is a detail the prior one for example table4 is a detail for table3 and so on. here is my schema:
PK1 Integer, Name1 varchar(15),...
PK2 Integer, PK1 Integer, -- This is foreign key to Table1.PK1 Name2 varchar(15),...
PK3 Integer, PK2 Integer, -- This is foreign key to Table2.PK2 Name3 varchar(15),...
PK4 Integer, PK3 Integer, -- This is foreign key to Table3.PK3 Name4 varchar(15),...
I need now to select records from Table1 based on certain value from Table4 for example when Table4.PK4 = 3.
So I tried like that:
Select * from table1 where PK1 in ( select PK1 from Table2 where PK2 in ( select PK2 from Table3 where PK3 in ( select PK3 from Table4 where PK4 = 3 )))
I got correct results but is it the best/optimized way or there is better SQL I should use ?
Why don’t you use all joins instead:
Select DISTINCT table1.* from table1 INNER JOIN table2 ON table1.PK1 = table2.PK1 INNER JOIN table3 ON table2.PK2 = table3.PK2 INNER JOIN table4 ON table3.PK3 = table4.PK3 WHERE table4.PK4 = 3
I think query engine should be smart enough to optimize your query, but at least from readability point of view the one I provided above is much more readable