select the columns in 1 table with provision in 2n

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

select the columns in 1 table with provision in 2n

Author

Topic

stu1
Starting Member

4 Posts

Posted - 2014-01-20 : 12:01:33

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

64 Posts

Posted - 2014-01-20 : 15:05:44

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

stu1
Starting Member

4 Posts

Posted - 2014-01-20 : 15:37:15

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?

stu1
Starting Member

4 Posts

Posted - 2014-01-20 : 15:47:37

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?

ITTrucker
Yak Posting Veteran

64 Posts

Posted - 2014-01-20 : 15:52:53

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

stu1
Starting Member

4 Posts

Posted - 2014-01-20 : 16:28:42

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-21 : 05:36:15

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources