Posts Tagged ‘Join’

Full Outer Join in MS Access

Posted: May 18, 2013 by MaxFraudy in MS Access, SQL
Tags: ,

Problem Statement: MS Access does not allow a full outer join query. What this means is that if I have two tables and I want all data from both of these tables, I cannot do it using one join.

Sql full outer join image

Image Credit – http://www.w3resource.com

Let’s take, for example, two tables – Table1 & Table2. Both these tables have employee information but not all employees are common. Table1 has emp_id 9 which is not there in Table2 while Table2 has emp_id 3 which does not appear in Table1. I want all employee IDs, common as well as non-common.


   Table1                           Table2
Emp_id	Salary			Emp_id	Name
1	10000			1	Tom
2	23000			2	Rock
4	40000			3	Thor
9	34000				

(more…)

Advertisements

Problem Statement: There are two tables and we need to get all the records from Table1 that are not in Table2.


   Table1                           Table2
Emp_id	Salary			Emp_id	Name
1	10000			1	Tom
2	23000			2	Rock
4	40000			3	Thor
9	34000				

Excluding Records from Table1 that Exist in Table2

Solution: It can be done in two ways, using Left Join OR using Exists condition. (more…)

Use of Self Join in Update Query

Posted: May 3, 2013 by Transformer in MS Access, SQL
Tags: , , ,

Some times one might need to update a record in a table based on a calculation on some field of the same table.It can not be done without using Self Join.
e.g. Consider the following data, there is a region named Region1 and this region includes C1 ,C2 and C3 cities.
So the population of Region1 is equal to the sum of population of these three cities.Now there are two more cities C4 and C5 that needs to be added in Region1.
Now population of Region1 should be updated by adding the population of C4 and C5 into it.


Geography	Population
C1	         136376
C2	         154644
C3	         176040
C4	         148793
C5	         138288
C6	         137754
C7	         141093
Region1	         467060
Region2	         278847

(more…)