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.
| Author |
Topic |
|
pe1826
Starting Member
6 Posts |
 Posted - 2014-03-07 : 11:53:09
|
| 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 | EquipTypeIt'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
352 Posts |
Posted - 2014-03-07 : 12:17:12
|
| [code]SELECT A.*, B.* FROM Financial A LEFT JOIN Equipment B ON A.PersonalID = B.PersonalID;[/code]djj |
 |
|
|
pe1826
Starting Member
6 Posts |
Posted - 2014-03-10 : 06:16:52
|
| 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 |
|
|
|
Muj9
Yak Posting Veteran
75 Posts |
Posted - 2014-03-12 : 11:49:19
|
| use inner join SELECT A.*, B.* FROM Financial A inner JOIN Equipment B ON A.PersonalID = B.PersonalID; |
|
|
|
|
|
|
|
|
Join Tables