How to get referenced values from another table?

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:

  • Error restoring database backup
  • How to handle sql transaction in this scenario?
  • How to script out stored procedures to files?
  • Excel VBA to SQL Server without SSIS
  • How can I connect to an external database from a sql statement or a stored procedure?
  • SQL Select COUNT(Value=Value) Possible?
  • enter image description here

    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.

    One Solution collect form web for “How to get referenced values from another table?”

    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
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.