How to pull data in one query using data from a different table?

Question

Ok, hopefully I worded the question well enough for someone to help me (hopefully).  I have two database tables: here are the designs-

tbSales                                                           tbRates

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

Answer 1

Ok that formatting is messed up and combining my tables so here they are again:

tbSales

Column Name: Data Type: Length:

Pk_ID(PK)  int 4

Origin char 3

Destin  char 3

Effd  smalldate 4 

Discount  money 8

 

tbRates

Column Name: Data Type: Length:

City_cd(PK) char  3

Locator char 3

Sales_Region char 3

Rate_Zone char 3

 Effd (PK)   smalldate 4

Answer 2

Hi nanowoman1,

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.

Answer 3

Hello Jian,

 

Here are the relation ships:

Parent table:

tbState

 

Child table:

tbSales

FKey_Name:

FK_tbSales_tbState

 

FKey_Col:

Origin

 

Ref_KeyCol

City_cd

________

Parent table:

tbState

 

Child table:

tblRates

FKey_Name:

 

FK_tblRates_tbState

 

FKey_Col:

Citycd

 

Ref_KeyCol

City_cd

 

Origin should be associated with the City_cd, and City_cd is many to many.

---

CNmitchell

Answer 4

NanoWoman...

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.

 

SELECT   tbSales.Pk_ID

        ,tbSales.Origin

        ,tbSales.Destin

        ,tbSales.Effd

        ,tbSales.Discount

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.

Thanks!

---

duncan davenport . data engineer and architect

Answer 5

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! 

---

CNmitchell

Answer 6

did you tried the query that duncan has suggested?