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
 select the columns in 1 table with provision in 2n
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stu1
Starting Member

4 Posts

Posted - 01/20/2014 :  12:01:33  Show Profile  Reply with Quote
I have two tables with the common column-name/ The first one include such columns as the name of football team, its country and budget. The second one include the team name, victories (quantity of it), lost games, and season. So I have the task to select such column as name, victories, lost games in 2nd table but only that ones that has budget over 1 mln? How to build select statement SELECT name, victories, lost g/ from TABLE2 WHERE budget >1000000 from TABLE1? Or should I equates the table1.name=table2.name??

ITTrucker
Yak Posting Veteran

USA
51 Posts

Posted - 01/20/2014 :  15:05:44  Show Profile  Reply with Quote
You want to join the two tables together on a common value (NAME in your example), so then you can select values from both tables and use both tables for exclusions in your WHERE clause.
Something like:

SELECT A.NAME, B.WINS, B.LOSS
FROM TABLE1 A INNER JOIN TABLE2 B ON A.NAME = B.NAME
WHERE A.BUDGET > 1000000
ORDER BY A.NAME
Go to Top of Page

stu1
Starting Member

4 Posts

Posted - 01/20/2014 :  15:37:15  Show Profile  Reply with Quote
What is the inner join? Why you use such syntax and what it means table1 A(why A, it would be bette without it), and what Table2 "On"-what is the last word?
Go to Top of Page

stu1
Starting Member

4 Posts

Posted - 01/20/2014 :  15:47:37  Show Profile  Reply with Quote
http://www.w3schools.com/sql/sql_join_inner.asp--here I read about INNER JOIN and ON keyword. So is it really workable? Amd why doubling the name table1 A but not table1.name: in this vein is it matter to begin this request as select table1.name--is it would be incorrect to write Select table2.name?
Go to Top of Page

ITTrucker
Yak Posting Veteran

USA
51 Posts

Posted - 01/20/2014 :  15:52:53  Show Profile  Reply with Quote
The A and B after the table names are just alias's to for less typing and sometimes to keep your query shorter if your table names are long, they're not required. The ON after the JOIN is the fields that link the two tables together, in your case you have a NAME in both so you can use that to link the two tables together.

You could use:
SELECT TABLE1.NAME, TABLE2.WINS, TABLE2.LOSS
FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.NAME = TABLE2.NAME
WHERE TABLE1.BUDGET > 1000000
ORDER BY TABLE1.NAME
Go to Top of Page

stu1
Starting Member

4 Posts

Posted - 01/20/2014 :  16:28:42  Show Profile  Reply with Quote
Thank you for the help. But it is only a part of my task. Except, creating database and table and its filling with data, I need to create such table that would have such columns as name,wins,lost where two last columns have results for all seazons. Should it looks like SELECT NAME, SUM(wins,losts) from table2 GROUP BY NAME IN-previous script?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/21/2014 :  05:36:15  Show Profile  Reply with Quote
yep it should be like

INSERT YourNewTableName
SELECT NAME, SUM(wins),SUM(losts) 
from table2 
GROUP BY NAME 


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.08 seconds. Powered By: Snitz Forums 2000