Selecting same column twice - 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

Selecting same column twice

Author

Topic

lordzsolt
Starting Member

3 Posts

Posted - 2011-11-16 : 01:09:14

Hey there, we have quite a problem here at science class.

We have two tables:
First table(table1):
- date of a football match (date)
- Team one's number (t1)
- Team two's number (t2)

Second Table: (table2)
- Name of a team (name)
- Number of a team (number)

So we have to show which team played with Germany (or other team).
The thing is, we can do it by showing the number of the teams (t1,t2), but we'd have to show the names of the teams, which not even my teacher knows how to do. Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 01:21:28

join to second table twice based on team one and team two number values and select name fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lordzsolt
Starting Member

3 Posts

Posted - 2011-11-16 : 01:25:13

Could you post the SQL code for it please? (I didn't really get the connections lesson)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 01:31:25

first make a try based on my explanation (i'm sure thats quite straightforward if you know concept of joins). If you hit a roadblock, post query used and then we will help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lordzsolt
Starting Member

3 Posts

Posted - 2011-11-16 : 01:38:28

This is what I got to:

Select name as TeamOne, name as TeamTwo, date
from table1, table2
Where (name="Germany" and t1=number and name="France" and t2=number)

I kinda don't get the concept of joins, that's why I asked for the code.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 01:47:22

you've not joined to table2 twice as i suggested
see how to do it below


Select a.date, b.Name AS TeamOne,b1.Name AS TeamTwo
from table1 a
inner join table2 b
on b.number = a.t1
inner join table2 b1
on b1.number = a.t2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources