Sql-server – Getting sql statement to refer to more than 2 tables

sql-server-2008

I have the following tables:

StaffName

============================================================================
|  Name    |   Income_Group   |    Educational_Level  |   Country          |
============================================================================
|  Jack    |   5              |    5                  |   1                |
|  Jill    |   3              |    3                  |   4                |
|  Jane    |   1              |    4                  |   6                |
|  June    |   4              |    2                  |   7                |
============================================================================

Country

==================================================
| ID   |  Country   |  Country_Description       |
==================================================
| 1    | America    |                            |
| 7    | Japan      |                            |
==================================================

IncomeGroup

=======================================================
| ID   |  Income_Range        |  Description          |
=======================================================
| 1    | Below US$2500        |                       |
| 5    | US$9000 to US$12000  |                       |
=======================================================

EducationalLevel

============================================================
| ID   |  Educational_Level   |  Country_Description       |
============================================================
| 1    | PhD                  |                            |
| 7    | Master               |                            |
============================================================

My intention is to get all the values from other table to the main table and display it something like the following:

Intended Result

=======================================================================================
|  Name    |   Income_Group              |    Educational_Level  |   Country          |
=======================================================================================
|  John    |   US$9000 to US$12000       |    PhD                |   America          |
|  KoKo    |   US$5000 to US$7000        |    Master             |   Japan            |
|  Kane    |   US$1000 to US$2000        |    B.Degree           |   Thailand         |
|  Ali     |   US$8200 to US$9200        |    College            |   Malaysia         |
=======================================================================================

I tried using the following sql:

select 
    s.name, s.Income_Group, s.Educational_Level, s.Country
from 
    StaffName s, Country c. IncomeGroup ig, EducationalLevel el 
where 
    s.Income_Group = c.ID 
    AND s.Educational_Level = ig.id 
    AND s.Country = el.id

But it return no results.

There is no foreign or primary keys for all tables.

What could I have been missing?

Best Answer

I appears that some of your syntax is not entirely correct in the question, however it may just be a typo. It appears also that you are attempting to use the old method of joining tables.

I noted also that you are having to return records from StaffName where there are no matches (nulls?) with the other tables. To still join, and to show only blanks where there are no matches, you will need to use an outer join. For simplicity, I have used only LEFT joins in the example below.

Also, note that I have returned the appropriate fields in your select list, referencing the joined tables, so as to get the result as shown in your requirement

select s.name, ig.Income_Range, el.Educational_Level, c.Country  
from StaffName s 
    left join Country c on s.Income_Group = c.ID
    left join IncomeGroup ig on s.Educational_Level = ig.id
    left join EducationalLevel el on s.Country = el.id

To better understand left joins, here is an excerpt from BOL.

Using Left Outer Joins

Consider a join of the Product table and the ProductReview table on their ProductID columns. The results show only the products for which reviews have been written. To include all products, regardless of whether a review has been written for one, use an ISO left outer join. The following is the query: 

USE AdventureWorks2008R2; 
GO 
SELECT p.Name, pr.ProductReviewID 
FROM Production.Product p 
    LEFT OUTER JOIN Production.ProductReview pr ON p.ProductID = pr.ProductID

The LEFT OUTER JOIN includes all rows in the Product table in the results, whether or not there is a match on the ProductID column in the ProductReview table. Notice that in the results where there is no matching product review ID for a product, the row contains a null value in the ProductReviewID column.

I suspect your statement regarding Primary Keys and Foreign Keys are purely to say that we needn't worry about the effect it may have on your joins. You may have your reasons for it, but please remember that having proper Primary Keys are fundamentally important to any database.

Related Question