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


  • 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 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

    quarta-feira, 18 de janeiro de 2012 18:52


Todas as Respostas

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


    Column Name: Data Type: Length:

    Pk_ID(PK)  int 4

    Origin char 3

    Destin  char 3

    Effd  smalldate

    Discount  money 8



    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

    quarta-feira, 18 de janeiro de 2012 18:58
  • 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.
    sexta-feira, 20 de janeiro de 2012 02:48
  • Hello Jian,


    Here are the relation ships:

    Parent table:



    Child table:











    Parent table:



    Child table:












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

    sexta-feira, 20 de janeiro de 2012 19:06
  • 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





    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

    quinta-feira, 1 de março de 2012 18:35
  • 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! 


    quinta-feira, 1 de março de 2012 19:14
  • did you tried the query that duncan has suggested?

    • Marcado como Resposta nanowoman1 segunda-feira, 26 de março de 2012 14:39
    sábado, 24 de março de 2012 05:19