Solved

Join two tables in sql server 2008

Posted on 2011-03-08
8
354 Views
Last Modified: 2012-05-11
I just created a new table and I want to join it to an existing table using the Primary key in the original table as a foreign key in the new table.  In sql server 2000I did this via a diagram.  I don't have diagrams here.  How can I complete this task otherwise?

Thanks!
0
Comment
Question by:Bob Schneider
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 15

Assisted Solution

by:derekkromm
derekkromm earned 425 total points
ID: 35075988
Right click the table with the PK, click design
From the top menu, Table Designer, Relationships

Add a relationship, on the "Tables and Columns Specification", click the "..." button to select the table/columns
0
 
LVL 1

Accepted Solution

by:
lucky20 earned 25 total points
ID: 35076032
There are various types of joins.. you can find here ..
SQL Joins

sql table1:
empid(pk)
empname



sql table2:
eid(fk) from <sqltable1>
deptid(pk)
dept_name


sql st:
select t1.empid, t1.empname, t2.dept_name from sqltable1 t1,sqltable2 t2 where t1.empid=t2.empid
0
 

Author Comment

by:Bob Schneider
ID: 35076128
On the solutioin by derekkrom, when I try to create a relationship it joins the PK table to itself?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Assisted Solution

by:OP_Zaharin
OP_Zaharin earned 50 total points
ID: 35076167
Sample:

TableA
   - Id (Primary Key)
   - Description

TableB
   - Id (Primary Key)
   - TableAId (FK to TableA)

select *
from
    TableA a
        left join
    TableB b
        on
            a.Id = b.Id
0
 
LVL 15

Assisted Solution

by:derekkromm
derekkromm earned 425 total points
ID: 35076168
Sorry, you want to do that on the table that contains the FK, not the PK. Apologies.
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35076208
this is a sql statement to do the foreign key:

CREATE TABLE STOCK
(Stock_ID integer primary key,
StockIn_Date datetime,
Description char(100),
Supplier_SID integer references SUPPLIER(SID),
Amount double);
0
 
LVL 23

Assisted Solution

by:OP_Zaharin
OP_Zaharin earned 50 total points
ID: 35076230
Further explanation - SUPPLIER is the foreign table name and SID is the primary key in SUPPLIER table
0
 

Author Comment

by:Bob Schneider
ID: 35076247
Awesome.  Thanks!
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

689 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question