Usuário com melhor resposta
Ok, hopefully I worded the question well enough for someone to help me (hopefully). I have two database tables: here are the designs-
Column Name: Data Type: Length: Column Name: Data Type: Length:
Pk_ID(PK) int 4 City_cd(PK) char 3
Origin char 3 Locator char 3
Destin char 3 Sales_Region char 3
Effd smalldate 4 Rate_Zone char 3
Discount money 8 Effd (PK) smalldate 4
So the primary keys in tbRates are City code and Effective date. The City Code pulls up a 3 letter code for each US city we have locations in. The Origin is a number that corresponds with the Rate Zone. Say Origin is 1 that would mean the city is in the mid west region, 2 means west coast region and Rate zones are the same except they can change periodically. What I was trying to make a query from tbSales to pull all its information '*' but instead of Origin or Destin showing as a number '1, 2, 3 ,4,...' I want to connect origin and destin to the citycd in tbRates to pull the 3 letter city code. For example if I have a 1 for my origin applicable City codes would be CLE, ORD, CDG, MSP, ect.
I want to make a connection with Origin and Destin in tbSales to Citycd in tbRates, so my where clause would be something like this:
select * from tbSales 'where Origin='CLE' and Destin='LAX'. instead of where Origin =1 and Destin=3 because way to many cities would come back from that query, I want to search for specific cities only.
I hope that you are able to understand this and offer help, I can elaborate more if need be. Thanking you all in advance for your time
Todas as Respostas
Could you please elaborate on the relationship between two tables and show us some sample data?
From your description, Origin is associated with Rate_Zone, right? Is there a one-to-one relationship betwwen Rate_Zone and City_cd?
If there is only one City_cd per Rate_Zone, I think you can use INNER JOIN to get your expected result.
SELECT tbSales.Pk_ID,tbSales.Origin,tbSales.Destin,tbSales.Effd,tbSales.Discount FROM tbSales INNER JOIN tblRates tblRates1 ON tbSales.Origin = tblRates1.Sales_Region INNER JOIN tblRates tblRates2 ON tbSales.Destin = tblRates2.Sales_Region WHERE tblRates1.City_cd = 'CLE' AND tblRates2.City_cd = 'LAX'
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Get or Request Code Sample from Microsoft
If you have any feedback, please tell us.
Here are the relation ships:
City_cdOrigin should be associated with the City_cd, and City_cd is many to many.
I think Jian's pretty close to what you are looking for. However, I think you indicated that the ORIGIN and DESTIN columns that contain the numbers should be linked to the Rate_Zone, not the Sales_Region.
FROM tbSales ts
JOIN tblRates trO
ON ts.Origin = trO.Rate_Zone
JOIN tblRates trD
ON ts.Destin = trD.Rate_Zone
WHERE trO.City_cd = 'CLE'AND trD.City_cd = 'LAX'
I don't believe the FK relationships to tbState are relevant to your immediate problem
Let us know if this works.
And please mark an answer in the forums if any of this helped.
duncan davenport . data engineer and architect
Thank you so much duncandavenport. I will try out the query and you are absolutely right in the fact that FK relationships are not relevant to my immediate problem. Thanks for your expertise in this matter. I will update as soon as I try out your query!