#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    3
    Rep Power
    0

    Joining 2 Tables on identicle columns eliminating duplicates


    Hello,

    I have 2 tables in the same database with different data except that one column (in both tables) Route_ID has the same route numbers (except one may not have all of the same routes as the other.

    I need to join the tables on those Route_IDs and I am having a difficult time understanding how the joins will work. Any help in understand how joins operate would be helpful.

    A little more info:

    Table A contains Route_ID plus a date

    Table B contains Route_ID and StartDate and EndDate

    I need to query out distinct on the Route_ID in both tables and also on a certain date in Table A and in the range of StartDate and EndDate for the same date.

    I have everything working but cannot figure out how to make the data join together so that my final list shows one route entry date and range for each route that falls into the parameters for both.

    Thanks,

    Sean
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,376
    Rep Power
    391
    Basically it would be like

    Code:
    select <columnsOfInterest>
      from route_table_1
      join route_table_2
        on route_table_1.route_id = route_table_2.route_id
     where route_table_1.date = <someDateValue>
       and <someDateValue> between route_table_2.startDate and route_table_2.endDate
    If you need more help you have to give some sample data and the expected result.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    3
    Rep Power
    0

    Thank you swampBoogie


    I actually figured it out yesterday once I understood the difference between inner and outer joins I ran:

    SELECT
    route_list_sb032206."Route_ID",
    route_list_sb032206."StartDate",
    route_list_sb032206."EndDate",
    route_list_sb032206."Carrier_ID",
    route_list_sb032206."SB_Carrier_Name",
    route_list_032206."Date",
    route_list_032206."Route_ID",
    route_list_032206."Carrier_ID",
    route_list_032206."Carrier_Name",
    route_list_032206."Occupant_Addr",
    route_list_032206."Occupant_City",
    route_list_032206."State",
    route_list_032206."Zip"
    FROM

    public."route_list_sb032206"

    INNER JOIN public."route_list_032206" on route_list_sb032206."Route_ID" = route_list_032206."Route_ID"

    Order by route_list_sb032206."Route_ID", route_list_032206."Route_ID";

    And this gave me what I needed.

    Thanks again for your response though.

    Sean

IMN logo majestic logo threadwatch logo seochat tools logo