Hi, trying to write some SQL to pul

[orangeseatbelt]

Hi, trying to write some SQL to pull data - can't get it to work so I'm hoping someone can explain what's wrong. Most interested in the explanation that just having a solution - thanks.

Two tables, linked on field code.

table - T1
code area
1 n
2 n
3 s
4 s
5 w


table - T2
code part value
1 a 5
1 b 5
1 c 5
2 c 25
3 c 10
3 d 1
4 a 5
4 b 15
4 x 15
4 y 15
4 z 100

Assuming the query is looking for a code of 1. From any related records in T2 it would find all PARTs (a, b, c) and then return any codes (in T2) that have that PART, along with the sum(value) of each group of CODE that contains the PART #


Results for code =1
part code sum(value)
a 1 15
a 4 150
b 1 15
b 4 150
c 1 15
c 2 25
c 3 11


If you're still reading ... well done.
This is still all fairly new to me, and after many attempts I've gotten as far as the example below, but what I can't seem to do is to add in extra fields (not shown above) from both t1 and t2 - then the query fails. I also feel as though I have too many nested selects...

Many thanks.


SELECT sum(value)
FROM t1 INNER JOIN t2 ON t1.code=t2.code
WHERE t1.code IN
(
SELECT t1.code
FROM t1 INNER JOIN t2 ON t1.code=t2.code
WHERE t2.code IN
(
SELECT t2.code
FROM t1 INNER JOIN t2 ON t1.code = t2.code
WHERE t1.code="1"
)
)
GROUP BY t1.code
ORDER BY t1.code DESC

 

[PHV]

A starting point:

SELECT A.code,A.area,B.part,C.code,(SELECT SUM(value) FROM T2 WHERE code=C.code) AS SumOfValue
FROM (T1 A
INNER JOIN T2 B ON A.code=B.code)
INNER JOIN T2 C ON B.part=C.part
ORDER BY 1,3,4

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Another way:

SELECT A.code,A.area,B.part,C.code,SUM(D.value) AS SumOfValue
FROM ((T1 A
INNER JOIN T2 B ON A.code=B.code)
INNER JOIN T2 C ON B.part=C.part)
INNER JOIN T2 D ON C.code=D.code
GROUP BY A.code,A.area,B.part,C.code

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886