SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Join Tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pe1826
Starting Member

United Kingdom
6 Posts

Posted - 03/07/2014 :  11:53:09  Show Profile  Reply with Quote
Hello,

Hope someone can help?

I have two tables that have have slightly different types of data relating to two different areas of our business financial support and equipment support. However each table does have one column that sits in both tables, an individual id column.

What I would like to do is combine the information from both tables in a query to give me an overall view of the support an individual has been provided with.

For instance;
Financial Support will have columns for..... 'Support ID', 'Personal ID', 'Amount Requested', 'Date Agreed', 'Support Type'
Equipment Loan will have columns for...... 'Loan ID', 'Personal ID', 'Date Loaned', Equipment Type'

I envisioned the results set to look something like below, where the Personal ID column from both tables is merged into one column.

PersID | SuppID | AmountReq Date | Agreed SuppType | LoanID | DateLoan | EquipType

It's likely that any individual may have many or none of either Financial or Equipment support records so there is likely to be multiple rows for each individual and each row will have one corresponding Financial Support ID or Equipment Loan ID.

I have attempted various permutations of Union etc but can't seem to get the results I'm looking for. Am I looking for the impossible?

I hope that makes sense.

In anticipation.
Paul

djj55
Constraint Violating Yak Guru

USA
341 Posts

Posted - 03/07/2014 :  12:17:12  Show Profile  Reply with Quote
SELECT A.*, B.* 
FROM Financial A 
LEFT JOIN Equipment B ON A.PersonalID = B.PersonalID;


djj
Go to Top of Page

pe1826
Starting Member

United Kingdom
6 Posts

Posted - 03/10/2014 :  06:16:52  Show Profile  Reply with Quote
HI djj,

Many thanks for the quick response.

This is nearly there, however I'm getting duplicated results. For instance if an individual has 2 Financial records and 3 Equipment records there are 6 result lines generated, whereas I was hoping for was to get 5 rows.... 2 for the Financials and 3 for the Equipment. And where there is Financial detail there would be no Equipment detail and then visa versa.

Paul
Go to Top of Page

Muj9
Yak Posting Veteran

United Kingdom
75 Posts

Posted - 03/12/2014 :  11:49:19  Show Profile  Reply with Quote

use inner join

SELECT A.*, B.*
FROM Financial A
inner JOIN Equipment B ON A.PersonalID = B.PersonalID;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000