SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help JOIN
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

VRP_Josh
Starting Member

5 Posts

Posted - 06/14/2012 :  10:05:13  Show Profile  Reply with Quote
Say I have two queries called lngproject and metalsminingproject and in each query is an award year, project type and project value column.

I want to JOIN the two together into a table with columns [Award Year], Sum of lng Projects, Sum of metalsmining Projects.

What's wrong with my code?

SELECT lngproject.[Award Year],
sum(lngproject.[project value]) AS LNG,
sum(metalsmining.[project value]) AS [Metals Mining]
FROM lngproject
JOIN metalsminingproject
ON lngproject.[Award Year]=metalsmining.[Award Year]

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 06/14/2012 :  10:27:09  Show Profile  Reply with Quote
Just what the eror message said, you need to use GROUP BY

SELECT lngproject.[Award Year],
sum(lngproject.[project value]) AS LNG,
sum(metalsmining.[project value]) AS [Metals Mining]
FROM lngproject
JOIN metalsminingproject
ON lngproject.[Award Year]=metalsmining.[Award Year]
GROUP BY lngproject.[Award Year]

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

VRP_Josh
Starting Member

5 Posts

Posted - 06/14/2012 :  10:33:02  Show Profile  Reply with Quote
I'm still getting "syntax error in FROM clause though
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 06/14/2012 :  11:09:22  Show Profile  Reply with Quote
I'm not seeing any syntax errors. Are you using Microsoft SQL Server?

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

VRP_Josh
Starting Member

5 Posts

Posted - 06/14/2012 :  11:36:06  Show Profile  Reply with Quote
I'm using SQL through Microsoft Access. This JOIN has me really frustrated...
Go to Top of Page

mhorseman
Starting Member

United Kingdom
18 Posts

Posted - 06/14/2012 :  11:38:35  Show Profile  Reply with Quote
One other thing, your two tables are Ingproject and metalsminingproject, but you're referring to metalsmining (NOT metalsminingproject) in your select and join?

Mark
Go to Top of Page

VRP_Josh
Starting Member

5 Posts

Posted - 06/14/2012 :  11:56:23  Show Profile  Reply with Quote
Mark you were right but after I fixed it, it's still giving me the same error. The code now is:

SELECT lngproject.[Award Year],
sum(lngproject.[project value]) AS LNG,
sum(metalsminingproject.[project value]) AS [Metals Mining]
FROM lngproject
JOIN metalsminingproject
ON lngproject.[Award Year]=metalsminingproject.[Award Year]
GROUP BY lngproject.[Award Year]
Go to Top of Page

mhorseman
Starting Member

United Kingdom
18 Posts

Posted - 06/15/2012 :  06:17:20  Show Profile  Reply with Quote
Sorry, hadn't noticed Access was involved. Not used it for ages - does it need you to specify "INNER JOIN" rather than just "JOIN"?

Mark
Go to Top of Page

VRP_Josh
Starting Member

5 Posts

Posted - 06/15/2012 :  07:09:10  Show Profile  Reply with Quote
Changed the join to inner join but now it's saying query is too complex. Any other ideas? I feel it's close but I don't know what exactly is wrong
Go to Top of Page

mhorseman
Starting Member

United Kingdom
18 Posts

Posted - 06/15/2012 :  11:40:50  Show Profile  Reply with Quote
Is your query really just the few lines you've entered, or is that psrt of a bigger query? Searching on "Query is too complex" gets results implying Access can't cope with too many columns, or too many joins .... etc.

Mark
Go to Top of Page

emam razib
Starting Member

Bangladesh
5 Posts

Posted - 06/17/2012 :  05:32:57  Show Profile  Reply with Quote
Hello every one!
I am just new of this forum and also this wide world of mange.
Anyone had some good suggestions and shier their experience in here.
I am really like air gear and loved to work but don't know much else.
Thank you all.

unspammed
Go to Top of Page

emam razib
Starting Member

Bangladesh
5 Posts

Posted - 11/10/2013 :  21:32:44  Show Profile  Reply with Quote
I am really like here gear and loved to work but don't know much else.

unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000