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
精彩评论