Retrieving Data from Two Table ( Interbase Tutorial # 11 )

We already have two tables in our database. Each table contains some populations. Now let’s see what we have in each table using SELECT statement.

    select * from employee

  ID NAME         AGE PHONENUMBER  
==================================

  1 Isaac Newton   30 555-5555  
  2 Blaise Pascal  25 555-1234  
  3 Archimedes     32 555-2233  
  4 George Boole   27 555-4521

    select * from salary

 SALARY_ID   EMPLOYEE_ID   SALARY BONUS TOTALSALARY 
======================================================= 

         1             1     1000   100        1100 
         2             2     2000   200        2200 
         3             3     3000   300        3300 
         4             4     4000   400        4400

Now we’re going to get employee’s name and their total salary. It will be a little bit complicated, because the ‘name’ column is in ‘employee’ table and the ‘totalsalary’ column is in ‘salary’ table. But don’t be worry, we can also retrieve data from two table using SQL statement. And the statement is :

    select name, totalsalary from employee, salary 
    where employee.id = salary.employee_id

The output is :

NAME                 TOTALSALARY
========================

Isaac Newton                          1100
Blaise Pascal                           2200
Archimedes                            3300
George Boole                          4400

The WHERE clause is for comparing the value of ‘id’ column in ‘employee’ table with the value of ‘employee_id’ in ‘salary’ table.

Now we’re going to get names and their total salaries of employee whose age is 30 year above. The statement will be :

select name, totalsalary from employee, salary 
where age>=30 and employee.id = salary.employee_id

The output is :

NAME         TOTALSALARY 
========================= 

Isaac Newton        1100 
Archimedes          3300 

Go to Previous Tutorial or Next Tutorial