Help in formulating sql query
Here is my scenario
Table 1: GID || Info1
Table 2: GID || Tb1GID (refers to GID of Table1) || Info2
Table 3: GID || Info3
开发者_运维百科Table 4: GID || Tb2GID (refers to GID of Table2 || Tb3GID (refers to Table3 GID || Value
Now I have to build an sql query to get the value, given a particular Info1, Info2, Info3. Essentially, I have to get the GID, of table 1 using info1, GID of table to , by mapping info2 and GID of table 1. And then get the GID of table 3 using info 3. And combine these to GIDs to get the value in table 4.
What is the most optimal way of constructing an sql query for this one?
Just join the tables on the id:s and match the info values in the condition:
select
t4.Value
from
[Table 1] as t1
inner join [Table 2] as t2 on t2.Tb1GID = t1.GID
inner join [Table 4] as t4 on t4.Tb2GID = t2.GID
inner join [Table 3] as t3 on t3.GID = t4.Tb3GID
where
t1.Info1 = @Info1 and
t2.Info2 = @Info2 and
t3.Info3 = @Info3
This communicates most clearly how the relations work, giving the query planner the best information to work with.
精彩评论