# Thread: How to join/merge 2 Tables

1. Registered User
Join Date
Nov 2009
Posts
223

## Unanswered: How to join/merge 2 Tables

Greetings.

How do I join 2 Tables which both have their own Primary Keys and have approx 15 different Fields each (total 30).

I have struggled to understand Join, Union, Merge etc. can this be done using a Wizard?

2. Moderator
Join Date
Mar 2009
Posts
5,442
You need to consider what happens to Columns and what happens to Rows in the resulting data set:

1. JOIN: TableA = 5 columns, TableB = 4 columns

- Columns:
The resulting data set = 9 columns (if you select all columns form TableA and TableB and specify no other criteria with a WHERE clause).

- Rows:
a) INNER JOIN:
Code:
```SELECT TableA.*, TableB.*
FROM TableA INNER JOIN
TableB ON TableA.Column1 = TableB.Column1;```
The resulting data set = only the rows that have the same value in TableA.Column1 and TableB.Column1.

b) a) LEFT JOIN
Code:
```SELECT TableA.*, TableB.*
FROM TableA LEFT JOIN
TableB ON TableA.Column1 = TableB.Column1;```
The resulting data set = all the rows in TableA. For the rows in TableB where TableB.Column1 do not match TableA.Column1, the 4 columns from TableB are Null.

c) a) RIGHT JOIN
Code:
```SELECT TableA.*, TableB.*
FROM TableA RIGHT JOIN
TableB ON TableA.Column1 = TableB.Column1;```
The resulting data set = all the rows in TableB. For the rows in TableA where TableA.Column1 do not match TableB.Column1, the 5 columns from TableA are Null.

d) CROSS JOIN:
Note: There is no operator for a CROSS JOIN in Access SQL, you simply use:
Code:
```SELECT TableA.*, TableB.*
FROM TableA, TableB;```
This operation performs the Cartesian product of the tables involved in the join. The resulting data set has the number of rows in the TableA multiplied by the number of rows in TableB.

Final notes on JOINS:
a) You can specify more than one condition in the ON part of the JOIN operation:
Code:
```SELECT TableA.*, TableB.*
FROM TableA INNER JOIN
TableB ON (TableA.Column1 = TableB.Column1) And
(TableA.Column2 > TableB.Column3);```
b) Nothing prevents you from specifying an additional criteria in a WHERE clause:
Code:
```SELECT TableA.*, TableB.*
FROM TableA INNER JOIN
TableB ON TableA.Column1 = TableB.Column1
WHERE TableA.Column3 Is Not Null;```
c) You can specify a subset of the columns from both tables. This will return 5 columns:
Code:
```SELECT TableA.Column1, TableA.Column2, TableA.Column3, TableB.Column1, TableB.Column2
FROM TableA INNER JOIN
TableB ON TableA.Column1 = TableB.Column1```
d) The columns involved in the JOIN (or in a WHERE clause) do not need to be present in the resulting set and the order of the columns is not fixed:
Code:
```SELECT TableA.Column5, TableA.Column2, TableA.Column3, TableB.Column2, TableB.Column4
FROM TableA INNER JOIN
TableB ON TableA.Column1 = TableB.Column1
WHERE TableA.Column4 Is Not Null;```
2. UNION: TableA = 5 rows, TableB = 4 rows. The resulting set = 9 rows.
Note: The number of columns must be the same in all members of a UNION operation:

a) If TableA = 5 Columns and TableB = 5 Columns:
Code:
```SELECT TableA.*
FROM TableA
UNION
SELECT TableB.*
FROM TableB;```
b) If TableA = 5 column, TableB = 4 column and the resulting data set must have 4 columns:
Code:
```SELECT TableA.Column1, TableA.Column2, TableA.Column3, TableA.Column4
FROM TableA
UNION
SELECT TableB.Column1, TableB.Column2, TableB.Column3, TableB.Column4
FROM TableB;```
Note: You can select any 4 columns from TableA, so this is valid too:
Code:
```SELECT TableA.Column1, TableA.Column3, TableA.Column4, TableA.Column5
FROM TableA
UNION
SELECT TableB.Column1, TableB.Column2, TableB.Column3, TableB.Column4
FROM TableB;```
c) If TableA = 5 column, TableB = 4 column and the resulting data set must have 5 columns:
Code:
```SELECT TableA.Column1, TableA.Column2, TableA.Column3, TableA.Column4, TableA.Column5
FROM TableA
UNION
SELECT TableB.Column1, TableB.Column2, TableB.Column3, TableB.Column4, Null
FROM TableB;```
Note: You can use any constant for replacing the missing 5th column in TableB provided that it's data type is compatible with the data definition of the correspondig column in TableA, so this is valid:
Code:
```SELECT TableA.Column1, TableA.Column2, TableA.Column3, TableA.Column4, TableA.Column5
FROM TableA
UNION
SELECT TableB.Column1, TableB.Column2, TableB.Column3, TableB.Column4, 'Missing Value'
FROM TableB;```
- For a more detailed explanation on JOINs, see: Join Fundamentals and Using Joins
- For a more detailed explanation on UNIONs, see: UNION (Transact-SQL)
Last edited by Sinndho; 11-18-12 at 08:57. Reason: Additional info supplied

3. Registered User
Join Date
Nov 2009
Posts
223
Sinndho,

Thanks for the excellent explanation. I think you should write a book on how to use Access - then you could make your millions?

I have printed your comments and will digest it over the next few days.

Thanks ever so much for helping.
Last edited by reddevil1; 11-18-12 at 11:25.

4. Registered User
Join Date
Nov 2009
Posts
223
Just to kick-start me, please woudl someone advise what is wrong with the code below to join all columns of two tables?

SELECT *
FROM tblCountry
JOIN tblEmployee;

Syntax error in FROM clause

5. Moderator
Join Date
Mar 2009
Posts
5,442
It must be either:
Code:
`INNER JOIN tblEmployee ON tblCountry.<Some Column> = tblEmployee.<Some Column>`
Or:
Code:
`LEFT JOIN tblEmployee ON tblCountry.<Some Column> = tblEmployee.<Some Column>`
Or:
Code:
`RIGHT JOIN tblEmployee ON tblCountry.<Some Column> = tblEmployee.<Some Column>`
Note: = in the criteria can be replaced by another comparison operator (<, >, <>, etc.)

6. Registered User
Join Date
Nov 2009
Posts
223
I am trying to join my 2 Tables together to make an Overall Report of Income and Expenditure.

My simplified test database has the following:-

INCOME TABLE
IncomeID

EXPENDITURE TABLE
ExpenditureID
AmountMoneySpent
DateMoneySpent

Therefore, I want 6 columns and 10 rows (eg. All the information from all Rows and all Columns from both Tables)

SELECT *
FROM tblIncome
INNER JOIN tblExpenditure
ON tblIncome.IncomeID=tblExpenditure.ExpenditureID;
This only gives 6 columns and 5 rows

SELECT tblIncome.*
FROM tblIncome
UNION
SELECT tblExpenditure.*
FROM tblExpenditure;
This only gives 3 columns and 10 rows

So how would I get all the information from both Tables (6 columns and 10 rows)?

7. Registered User
Join Date
Nov 2009
Posts
223
Oh my giddy aunt!!!

FYI - the test database has 5 Income Rows and 5 Expenditure Rows = 10 rows.

SELECT tblIncome.*, tblExpenditure.*
FROM tblIncome, tblExpenditure;
The above is the CrossJoin kindly suggested by Sinndho. This provides 6 columns but 25 rows.

I am trying to get 6 columns and 10 rows.
I would expect 5 (Income) rows to have data in 3 of the columns (with the other 3 columns blank.

Maybe i am not explaining myself well?

My overall aim is to have a Report showing all Income and Expenditure in one Report but I am not having much success as I cannot work out the design of a "joined?" query/report.

8. Registered User
Join Date
Nov 2009
Posts
223
I am confused and think I am going round in circles??? Please can I summarise this again.

My simplified test database has the following:-

INCOME TABLE
IncomeID

EXPENDITURE TABLE
ExpenditureID
AmountMoneySpent
DateMoneySpent

I want to produce a Profit/Loss Report which will look something like this:-

ID Amount Recvd Amount Spent Date
1 10,000 1/1/12
2 12,000 15/1/12
3 10,000 22/1/12
1 5,000 23/1/12
4 15,000 24/1/12
2 20,000 30/1/12

9. Registered User
Join Date
Nov 2009
Posts
223
I have attached a sample Report......

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•