Inner, Left, Right Joins .... Outer?

[pdbowling]

I have a couple of tables that I want to combine and do matches but keep the non matches from Both tables. Is there such a thing as an outer join? or is it a matter of tricky manipulation?

Table 1
Vehicle...........Motion
F001..............123
F003..............234
F004..............345

Table 2
UnitID.......Reading.......ReadingDate.......Count........WoNum
F001.........1234............9/25/2003...........1................12345
F002.........2345............9/26/2003...........3................23456
F004.........3456............9/26/2003...........1................34567


Desired Query

UnitID....Reading....Motion....ReadDate....Count....WoNum
F001......1234.........123........9/25/2003....1............12345
F002......2345......... .........9/26/2003....3............23456
F003...... .........234........ .... ............
F004......3456.........345........9/26/2003....1............34567

Thanks all
PB

 

[Mile-O]

Is there such a thing as an outer join?

Left and Right joins are the "short term" used for them. These actually are OUTER joins.

 

[dcx693]

Quick tip: an inner join shows you results that match in all tables connected.

An outer join (the ones with the arrows) take all records from the starting table and only those records that match up in the target table (the one being pointed to).

A left or right outer join is basically the same thing. It just depends on the direction you start from.

Note also that Inner and outer joins are equi-joins (where matching things up depends on a value from one table equaling the value on another table) as opposed to non-equi-joins where you join tables without using join lines, often using operators like "Like", Instr() and even Between, but that may have only helped to confuse you.

 

[pdbowling]

Thanks

Left, then right then union.....

I'm thinking that I'll be seeing 2 copies of the matches.

I'll try querying the union for "Distinct"

THanks all
PB

 

[dcx693]

pdbowling, no need. I don't even think there is a DISTINCT option with Union queries - they automatically screen out duplicates. It's only if you want duplicates that you have to indicate it with the ALL option.

 

[pdbowling]

Great

Thank you all.
PB