Pulling data from 2 tables - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Pulling data from 2 tables

Author

Topic

sumit381
Starting Member

6 Posts

Posted - 2008-07-25 : 14:47:23

Hello I am fairly new to SQL, and I writing a query that will pull data from 2 tables, Table_1 and Table_2. I have list the columns below for each table.

Table_1:
ID----Have_OS----Wanted_OS
---------------------------
1 ---- 1 ---- 2
2 ---- 2 ---- 3

Table_2:
OS_ID----OS_Name
---------------------------
1 ---- WinXP
2 ---- WinVista
3 ---- WinServer2008

The numbers in columns Have_OS and Wanted_OS in Table_1 correspond to the OS_ID column in Table_2. I am trying to write a query that will display the results in the following format.

ID Have_OS Want_OS
------------------------
1 WinXP WinVista
2 WinVista WinServer2008

I have written the following queries to get the OS_Name associated with the ID, but they are 2 separate queries.

select t1.ID, t2.OS_ID as 'Have OS'
from Table_1 t1, Table_2 t2
where t1.Have_OS = t2.OS_ID;

select t1.ID, t2.OS_ID as 'Want OS'
from Table_1 t1, Table_2 t2
where t1.Want_OS = t2.OS_ID;

These queries will give me the names of the OS, but they are in 2 separate outputs. How can I combine them to get the output I explain above? I looked at JOIN statements but I was unsuccessful with that approach. Any help will be greatly appreciated.

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 14:57:42

[code]SELECT t1.ID,t2.OS_Name AS Have_OS,t3.OS_Name AS Wanted_OS
FROM Table_1 t1
INNER JOIN Table_2 t2
ON t2.OS_ID=t1.Have_OS
INNER JOIN Table_2 t3
ON t3.OS_ID=t1.Wanted_OS[/code]

sumit381
Starting Member

6 Posts

Posted - 2008-07-25 : 15:08:28

Thank you visakh16, that worked!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources