Need help with subqueries

[sumdumgai]

Hello. I need some help with joins and using subqueries, and will appreciate any help. To make it as simple as possible, let's say I have four tables, call them T1 (large), T2 (small), T3 (large) and T4 (small).

T1 has fields ID and ZIP.
T2 has fields ZIP.
T3 has fields ID, NAME, VAL
T4 has fields NAME.

First join is to get all T1 records where T1.ZIP and T2.ZIP match resulting in A.
Second join is to get all T3 records where T3.NAME and T4.NAME match resulting in B.
Third join is to get all B records where B.ID and A.ID match resulting in C.

Then I need to Sum C.VAL

I just can't get around syntax errors and need some help. Thanks for your time and help.

 

[CJ_London]

something like

SELECT sum (val) 
FROM (t1 inner join T2 on t1.zip=t2.zip) A 
     inner join (t3 inner join t4 on t3.name=t4.name) B 
         on A.ID=B.id

I presume your names are generic, name is a reserved word. Note that the two subqueries cannot be created in the query grid per se, you need to create in the sql view.

Alternative is to create the two queries A and B in separate queries and join them in a third query C

 

[sumdumgai]

Thanks for quick reply. I can see what your suggestion is trying to do. 'Name' is generic in my example. I've plugged in actual table names and field names but get a syntax error on the 'A'.

Here is what I've tried:

select A.ID
FROM (Demo INNER JOIN [ZipTab] ON Demo.[Zip Code] = [ZipTab].[Zip Code]) A
INNER JOIN (Data INNER JOIN [NameTab] ON Data.[LastName] = [NameTab].LastName) B
on A.ID=B.ID

 

[sumdumgai]

Access has no problem with following 'From' clause but SQL Server gives syntax error:

FROM T1 INNER JOIN T2 ON T1[Zip Code] = T2.[Zip Code]
INNER JOIN (T3 INNER JOIN T4 ON T3.[LastName] = T4.LastName) ON T3.ID = T1.ID
INNER JOIN T5 ON T3.Grade = T5.Grade

Any help would be appreciated. Thanks.

 

[CJ_London]

my bad - forgot the select * from

 SELECT sum (val) 
FROM ([COLOR=red]SELECT * FROM[/COLOR] t1 inner join T2 on t1.zip=t2.zip) A 
     inner join ([COLOR=red]SELECT * FROM[/COLOR] t3 inner join t4 on t3.name=t4.name) B 
         on A.ID=B.id

 

[sumdumgai]

Thank you again for your time. I revised my SQL to better reflect what I am trying to do and to use your suggestion. Here it is:

SELECT P.[EmpID], P.[EmpNbr], P.[EmpInd], P.[FirstName], P.[LastName],
P.Address, P.City, P.State, P.[ZipCode], P.[Specialty],
B.[ProdID], [PayKey].[PayType], [MDMD].Skill,
Convert (varchar(10),SUM(CAST(B.[TotalSales] as Int))) AS [SumOfTotalSales]
INTO [Sales by Skill Type]
FROM (SELECT * FROM WorkForce INNER JOIN [ZipTable] ON WorkForce.[ZipCode] = [ZipTable].[ZipCode]) P
INNER JOIN (select * from SalesData INNER JOIN [MDMD] ON SalesData.[ProdID] = [MDMD].ProdID) B
on P.[EmpID]=B.[EmpID]
GROUP BY P.[EmpID], P.[EmpNbr], P.[EmpInd], P.[FirstName], P.[LastName],
P.Address, P.City, P.State, P.[ZipCode], P.[Specialty],
B.[ProdID], [PayKey].[PayType], [MDMD].Skill
ORDER BY Convert (varchar(10),SUM(CAST(B.[TotalSales] as Int)))
DESC

Now I am getting these errors:

Msg 8156, Level 16, State 1, Line 7
The column 'ZipCode' was specified multiple times for 'P'.
Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "PayKey.PayType" could not be bound.
Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "MDMD" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "PayKey.PayType" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "MDMD.Skill" could not be bound.

Note I changed 'A' to 'P' to try to fix but no go.

Thanks again.