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
 Help JOIN

Author  Topic 

VRP_Josh
Starting Member

5 Posts

Posted - 2012-06-14 : 10:05:13
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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-14 : 10:27:09
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 - 2012-06-14 : 10:33:02
I'm still getting "syntax error in FROM clause though
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-14 : 11:09:22
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 - 2012-06-14 : 11:36:06
I'm using SQL through Microsoft Access. This JOIN has me really frustrated...
Go to Top of Page

mhorseman
Starting Member

44 Posts

Posted - 2012-06-14 : 11:38:35
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 - 2012-06-14 : 11:56:23
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

44 Posts

Posted - 2012-06-15 : 06:17:20
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 - 2012-06-15 : 07:09:10
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

44 Posts

Posted - 2012-06-15 : 11:40:50
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

5 Posts

Posted - 2012-06-17 : 05:32:57
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

5 Posts

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

unspammed
Go to Top of Page
   

- Advertisement -