Creating a view in SQL Server

question Published at Dev

Question

I have 2 tables:

Contracts (ContactID, ContractNo, estimatedCost, CurrencyEstimatedCostID, ContractCost, CurrencyContractCostID)
CurrencyTypes (CurrencyID, CurrencyTypeName).

I want to view the contract table with CurrencyTypeName in place of the Foreign Keys CurrencyEstimatedCostID and CurrencyContractCostID in the view (in the SQL Server view).

I use this query but it return just one column for CurrencyTypeName (I want one column for each of CurrencyEstimatedCostID and CurrencyContractCostID).

SELECT 
    Contracts.contractID, Contracts.ContractNo, CurrencyTypes.CurrencyType
FROM 
    Contracts 
INNER JOIN 
    CurrencyTypes ON Contracts.CurrencyEstimatedCostID = CurrencyTypes.CurrencyType 
                     AND Contracts.CurrencyContractCostID = CurrencyTypes.CurrencyType

How do I change the query?

marc_s

Not 100% sure if I understand what you're trying to do - but if you have two columns that both act as foreign key into the CurrencyTypes table, you need to join twice:

SELECT 
    c.contractID, c.ContractNo, 
    EstimatedCostCurrency = ct1.CurrencyType, 
    ContractCostCurrency = ct2.CurrencyType
FROM 
    Contracts  c
INNER JOIN 
    CurrencyTypes ct1 ON c.CurrencyEstimatedCostID = ct1.CurrencyID
INNER JOIN 
    CurrencyTypes ct2 ON c.CurrencyContractCostID = ct2.CurrencyID

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-03-24

Comments

0 comments

TOP Ranking

Article

Creating a view in SQL Server

Creating a view in SQL Server

pump.io port in URL

How to import an asset in swift using Bundle.main.path() in a react-native native module

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

Inner Loop design for webscrapping

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on '127.0.0.1:3306' (111 Connection refused)

Removed zsh, but forgot to change shell back to bash, and now Ubuntu crashes (wsl)

ggplotly no applicable method for 'plotly_build' applied to an object of class "NULL" if statements

How to run blender on webserver?

Resetting Value of <input type="time"> in Firefox

Converting a class method to a property with a backing field

Ambiguous use of 'init' with CFStringTransform and Swift 3

Execute ./script.sh with a crontab

How to set tab order for array of cluster,where cluster elements have different data types in LabVIEW?

How to pass data to the ng2-bs3-modal?

Retrieve Element Tag Value XML Using Bash

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

SQL Server : need add a dot before two last character

Making Array From Page Elements in jQuery

Laravel's ORM sync with timestamps doesn't update timestamps

Do animations stop css changes after animation completion?