Inner Join to Nested Query Result

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Inner Join to Nested Query Result

Author

Topic

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-09-04 : 10:20:19

Orders is a table with customer & id fields
and I have a nested query that returns Customer,QtyItems. I need the get the QtyItems for each ID..should be just an inner join... BUT

This does NOT work ...

Select Orders.Customer,orders.id,QtyItems
FROM Orders INNER JOIN
(
Select Customer, Count(items) as QtyItems
From ( .......) AS D
Group by Customer

)
ON Orders.Customer =Customer

Ideas ??? Thanks !!!

andrewcw

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-04 : 10:23:05

Select Orders.Customer,orders.id,s.QtyItems
FROM Orders INNER JOIN
(
Select Customer, Count(items) as QtyItems
From ( .......) AS D
Group by Customer
)s
ON Orders.Customer =s.Customer

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-09-04 : 11:23:22

I get Incorrect syntax near the keyword 'SELECT'
Incorrect syntax near ')'. Not sure why - my query models my sample I posted quite closely - just aggreagate field name different. Ideas ? Thanks

andrewcw

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-04 : 11:25:49

Please post the whole query that you tried executing.

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-09-04 : 12:01:44

Select CustomerLineNumber.Customer, CustomerLineNumber.LineNumber,S.LNFails
FROM CustomerLineNumber innerJoin
(
SELECT Customer, COUNT(PassStatus) AS LNFAILS
FROM (SELECT SUBSTRING(CSB_FKey, 1, 5) AS Customer, PassStatus
FROM dbo.TestFlightProcedureDynamic
WHERE (PassStatus <> 'PASS') AND (SUBSTRING(CSB_FKey, 1, 1) = 'Y')) AS D
GROUP BY Customer
) S
ON CustomerLineNumber.Customer =s.Customer

andrewcw

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-04 : 12:11:53

there is a space needed between "inner" and "join". Not sure if this is the only problem though.

Select CustomerLineNumber.Customer, CustomerLineNumber.LineNumber,S.LNFails 
FROM CustomerLineNumber inner Join
(
SELECT Customer, COUNT(PassStatus) AS LNFAILS
FROM (SELECT SUBSTRING(CSB_FKey, 1, 5) AS Customer, PassStatus
FROM dbo.TestFlightProcedureDynamic
WHERE (PassStatus <> 'PASS') AND (SUBSTRING(CSB_FKey, 1, 1) = 'Y')) AS D
GROUP BY Customer
) S
ON CustomerLineNumber.Customer =s.Customer

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-09-04 : 13:15:20

EEEEK ! THANKS !!! That was it....!!! somehow I had let it come together Thanks all for showing me how to get the data to flow into the inner join.

andrewcw

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-04 : 13:52:58

ohh great! you're welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources