I have the following tables:


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


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


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


| 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:

    s.name, s.Income_Group, s.Educational_Level, s.Country
    StaffName s, Country c. IncomeGroup ig, EducationalLevel el 
    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?

  • That SQL statement won't even run with the . after c and , after el before where etc – 孔夫子 Apr 23 '13 at 9:56
  • @孔夫子: I suspect that was just a typo. – RoKa Apr 23 '13 at 10:09
  • @RoKa, yeah, there is a typo for the , after el – Jack Apr 23 '13 at 10:14

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; 
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.

    select s.name, ig.Income_Group, 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;
  • Strange, when you perform Join, I have many missing data not appearing at all, which I experience previously. How do I get all the data back even though it might have NULL values inside it (inside all the columns except the name in the table StaffName)? – Jack Apr 23 '13 at 10:11
  • Use LEFT [outer] JOINs to always show the rows from the StaffName table even if there are no matches from the other tables – 孔夫子 Apr 23 '13 at 10:12

If you want to have the fish today, you can use Confucius' query and it will surely work fine.

If you expect to learn how to fish yourself, you can have a look at SQL books in order to solve similar problems that will crop up soon. I can recommend you Learning SQL by Alan Baulieu as a very good starter.

Besides this, is no primary key a choice or a constraint you have to face?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.