Help in formulating sql query

Question

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?

What queries have you gotten so far?

Oded
– Oded

2010-05-16 16:36:53 +00:00
Commented May 16, 2010 at 16:36 — Oded
– Oded, Commented May 16, 2010 at 16:36

Guffa · Accepted Answer · 2010-05-16 16:44:53Z

2

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.

answered May 16, 2010 at 16:44

Guffa

703k111 gold badges760 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

OMG Ponies Over a year ago

You can also move the WHERE clauses for t2 and t3 into their respective JOINs.

Collectives™ on Stack Overflow

Help in formulating sql query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related