Complicated SQL Query

I am trying to make a sql query following these rules:

basically i have 2 p开发者_如何学编程arent tables, A and B. B has a child table called C and A has a child called D. when i select a value from B i get a range of records from C. this list of records dictates what i pull from A and then i need all the info from D that relates to each record pulled from A.

i have built a query that gets the records from A but im lost when going down another level.

SELECT A.ID
FROM (A
INNER JOIN A2
      ON A.IDV = A2.IDV
      AND A.Version = A2.CurrentVersion)
      LEFT JOIN C ON C.LANGUAGE = A.LANGUAGE
      INNER JOIN B ON C.PARENTID = B.ID
      WHERE (A2.Enabled = 1)
      AND (A.NUMBER = 00596205017)
      AND (B.NAME = 'BLAH');

Thanks in advance

R

---

Tables

create table A (AID int)
create table B (BID int, AID int)
create table C (CID int, BID int)
create table D (DID int, AID int)

Data

insert into A values (1),(2),(3)
insert into B values (1, 1),(2, 1),(3, 2),(4, 3)
insert into C values (1, 1),(2, 1),(3, 2),(4, 3)
insert into D values (1, 1),(2, 1),(3, 2),(4, 3)

Query

select D.*
from A as A
  inner join D as D
    on A.AID = D.AID
where A.AID in (select B.AID
                from B as B
                  inner join C as C
                    on B.BID = C.BID)

Result

DID         AID
----------- -----------
1           1
2           1
3           2