Join types

[Sandy]

Can anyone recommend a website that explains how the different join types
work in Access 2003?
Sandy

 

[Rick Brandt]

Can anyone recommend a website that explains how the different join
types work in Access 2003?
Sandy

What more do you need than what you get in the query designer? If you
double-click a join line in the query designer the dialog that lets you choose
gives an explanation of each.

Standard Join (no arrow heads on the line)
Return all rows where there is a matching row in BOTH tables.

Left / Right Join (arrow head pointing at one table)
Return all rows from the table that the arrowhead is NOT pointing at even when
there is no matching row in the table the arrowhead IS pointing at.

Left or Right is just determined by which table the arrowhead is pointing at.
When it is pointing away from the table you started the join line from (first
one mentioned in the FROM clause when looking at the SQL) then it is a Left
join. When pointing at the other table it is a Right join.

 

[Sandy]

I was hoping for a bit to do with self join. Which is a bit more than in
query designer.
Sandy

 

[Rick Brandt]

I was hoping for a bit to do with self join. Which is a bit more than
in query designer.
Sandy

Not sure I understand. Self join as in a table joined to itself? Just add the
same table twice to the query designer and create a join just as you would
otherwise. Access will automatically alias the second instance by adding "_1"
to the end of the name.

 

[Rob Parker]

Hi Sandy,

You can try this page, specific to SQL for the Microsoft Jet engine (the
engine used in Access):
http://www.microsoft.com/technet/prodtechnol/sql/70/proddocs/msjet/jetch04.mspx?mfr=true

HTH,

Rob

 

[Sandy]

Thanks Rob, just what I was after.
Sandy

Hi Sandy,

You can try this page, specific to SQL for the Microsoft Jet engine (the
engine used in Access):
http://www.microsoft.com/technet/prodtechnol/sql/70/proddocs/msjet/jetch04.mspx?mfr=true

HTH,

Rob