Solved

Join two tables in sql server 2008

Posted on 2011-03-08
8
353 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
Industry Leaders: 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

Independent Software Vendors: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ASP and Extracting XML 7 28
Using this function 4 41
SP result not being displayed 5 42
SQL Query Across Multiple Tables - Help 5 20
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 …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

749 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