How to get referenced values from another table?

Question

Not being this my job, I still have only very little knowledge of SQL, my question is to get directions on how to approach this problem. I hope the question is not too generic or meaningless:

I have several tables in a db. Few of them are basic tables, list of countries, list of customers and so on. These basic tables are used to support values in dropDownLists on other grids where users use preset values for certain fields and they also fill in other fields manually. Something like this:

Now, I am working on a dynamic report that points to the table where I need to get the actual values and not the references.

As I said, the problem I have is that those preset values in that particular table I am working on, are id_references (id_Country, id_Customer, ..) while I need the actual value they represent. What should I do to get the actual values? If I point my report to different tables I loose the meaning/link between the records.

Answer 1

For Example if you are trying to get A country's Name by using Country_ID in this Project table shown in your question, You could do something like

SELECT C.CountryName_Colummn, C.SomeOtheColumn, ......
FROM Projects P INNER JOIN Countries C
ON P.id_Country = C.id_Country
WHERE SomeColum = 'Your Condition'

1) SELECT Clause you select the columns you need in your result set.
2) FROM you Mention table(s) name(s)
3) ON Clause you define the relationship between the tables Say Projects table had a Column id_Country which refers to id_Country in Countries table defines the relationship between these two tables.
4) After you have Selected the column list, Source of data (tables), Their relationship (On Clause) then you can filter the number of rows returning from you query, like you can something like id_Country
WHERE C.CountryName = 'UK' will return results only from UK.
5) In from Clause the Letters 'C' and 'P' are alias so we dont have to type full table names again n again, makes our code simpler and easier to read and debug.

No matter how many tables you have to join to get the required data as long as you can define the relationship between them table in your query it should work fine. SQL server rarely you would find all the required data in one table normally you would join 2-3 tables or more tables. For example you want some data that is present in 3 different tables you would join them all three in one query something like this ...

SELECT C.CountryName_Colummn, C.SomeOtheColumn, ......
FROM Projects P INNER JOIN Countries C
ON P.id_Country = C.id_Country
INNER JOIN Table_3_Name T3
ON T3.CommonColumn = P.SomeCommonColumn (it will be a common column between table 3 and Projects OR Countries)
WHERE SomeColum = 'Your Condition'

But you really need to look in joins as you can do different types of joins between tables, Hers in this example I used INNER JOIN which returns only the matching rows between all these tables, you could do LEFT JOIN or RIGHT JOIN.
LEFT JOIN returns all the rows from the table on LEFT side of the JOIN key word and only matching rows from other tables.
RIGHT JOIN returns all the rows from the on the right side of the JOIN key word and only mantching rows from other tables.

Queries with only desired Columns

Select customers.customer_name, Products.Product_type, Manufacturers.Manuf_name 
from Projects inner join customers
on     customers.cust_id= Projects.proj_cust_id
inner join Products
on     Products.prod_id= Projects.proj_prod_id
inner join Manufacturers
on     Manufacturers.man_id= Projects.proj_man_id

Making use of Alias will give you exactly the same reuslt but easy to read code also try this ....

Select C.customer_name, Prod.Product_type, M.Manuf_name 
from Projects Proj inner join customers C
on     C.cust_id= Proj.proj_cust_id
inner join Products Prod
on     Prod.prod_id= Proj.proj_prod_id
inner join Manufacturers M
on     M.man_id= Proj.proj_man_id