Querying 3 tables - SQL Server Forums

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

Querying 3 tables

Author

Topic

unikoman
Starting Member

32 Posts

Posted - 2009-02-15 : 20:15:13

Hi

I have the following query, the first part works but I can't figure out out how to join the 3rd TABLE ( products) below the UNION.

select dbo.P_Assets.AS_ID, dbo.P_Assets.AS_ExtAccountID, dbo.P_Assets.AS_DESC, SUM(dbo.P_Trans.TX_Quantity) TOTAL_SUM

FROM dbo.P_Assets INNER JOIN
dbo.P_Trans ON dbo.P_Assets.AS_ID = dbo.P_Trans.TX_AS

WHERE dbo.P_Assets.AS_ExtAccountID not like 'CLRV-%'

GROUP BY dbo.P_Assets.AS_ID, dbo.P_Assets.AS_ExtAccountID, dbo.P_Assets.AS_DESC

UNION

SELECT dbo.Products.APIRCode
FROM dbo.PRODUCTS INNER JOIN
dbo.P_ASSETS ON DBO.PRODUCTS.ID = DBO.P_ASSETS.AS_PR

Thanks in advance for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-15 : 21:11:17

[code]select dbo.P_Assets.AS_ID, dbo.P_Assets.AS_ExtAccountID, dbo.P_Assets.AS_DESC,x.TOTAL_SUM,dbo.PRODUCTS.APIRCode,...
FROM dbo.P_Assets
INNER JOIN (SELECT dbo.P_Trans.TX_AS,SUM(dbo.P_Trans.TX_Quantity) TOTAL_SUM
FROM dbo.P_Trans
GROUP BY dbo.P_Trans.TX_AS)x
ON dbo.P_Assets.AS_ID = x.TX_AS
INNER JOIN dbo.PRODUCTS
ON DBO.PRODUCTS.ID = DBO.P_ASSETS.AS_PR
WHERE dbo.P_Assets.AS_ExtAccountID not like 'CLRV-%'
[/code]

unikoman
Starting Member

32 Posts

Posted - 2009-02-15 : 21:49:46

thanks visakh16!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-15 : 23:20:21

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources