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

Basically it would be like



If you need more help you have to give some sample data and the expected result.

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