Joins in Access

[Malcolm WTII]

Before I proceed down a long path, I was wondering if anyone could clarify a
couple things for me. I usually work on SQL server, and I'm not trying to
manipulate some data in Access. Does Access have 'left outer join'? or
just outer join? What is the difference either way with SQL Server? I've
included an excerpt of some code that I wrote for SQL server, and want to
run the equivalent on Access now. This follows suit with my other posts of
trying to go backwards merging several tables into one for purposes of
conversion. Opinions?

Thanks,
M

UPDATE Names
SET Phone2 = p2.phone,
phone2type = p2.note,
phone3 = p3.phone,
phone3type = p3.note,
phone4 = p4.phone,
phone4type = p4.note,
phone5 = p5.phone,
phone5type = p5.note,
phone6 = p6.phone,
phone6type = p6.note
FROM Names
LEFT OUTER JOIN Phones AS p2
ON p2."id" = Names."id"
AND (SELECT COUNT(*)
FROM Phones
WHERE "id" = p2."id"
AND primarykey <= p2.primarykey) = 1
LEFT OUTER JOIN Phones AS p3
ON p3."id" = Names."id"
AND (SELECT COUNT(*)
FROM Phones
WHERE "id" = p3."id"
AND primarykey <= p3.primarykey) = 2
LEFT OUTER JOIN Phones AS p4
ON p4."id" = Names."id"
AND (SELECT COUNT(*)
FROM Phones
WHERE "id" = p4."id"
AND primarykey <= p4.primarykey) = 3
LEFT OUTER JOIN Phones AS p5
ON p5."id" = Names."id"
AND (SELECT COUNT(*)
FROM Phones
WHERE "id" = p5."id"
AND primarykey <= p5.primarykey) = 4
LEFT OUTER JOIN Phones AS p6
ON p6."id" = Names."id"
AND (SELECT COUNT(*)
FROM Phones
WHERE "id" = p6."id"
AND primarykey <= p6.primarykey) = 5

 

[Guest]

Here is how a left join will look in Access, it does not contain the word
OUTER:

SELECT tblPartsQtyOnHand.CONTRACT,
tblPartsQtyOnHand.PART_NO,
tblFIFO.QUANTITY
FROM tblPartsQtyOnHand LEFT JOIN tblFIFO ON (tblPartsQtyOnHand.PART_NO =
tblFIFO.PART_NO) AND (tblPartsQtyOnHand.CONTRACT = tblFIFO.CONTRACT);

 

[Van T. Dinh]

There is a big difference in Update Query between JET SQL and SQL Server
SQL: there in no FROM Clause in JET SQL

In JET SQL, you use:

UPDATE tblA LEFT JOIN tblB ...
SET tblA.Field1 = tblB.Field1 ...
WHERE ...

Check the JET SQL Reference section of Access Help for the full JET UPDATE
SQL syntax.

 

[Michel Walsh]

Hi,


There is only cross (without using the word), inner, left outer and
right outer (without the use of the word outer) join.

Outer join ON clause implying just one table is moved to the where
clause, making the result more "like expected", but different that with the
standard.


SELECT a.city
FROM authors As a LEFT JOIN authors As b
ON a.city=b.city AND b.city='Iqaluit'


will result, in MS SQL Server, with all records from table authors, but,
with Jet, since no record have Iqaluit under City, no record will be
returned. A whole world of difference, indeed: every thing versus nothing at
all.



In Jet, use [ ] rather than " " to delimit an illegal name. " " is
use, in Jet, to denote a literal string.


You may use virtual tables, to force the constant = something to occur
before the join, rather that using the ON clause, as you do now.

rather than:

LEFT OUTER JOIN Phones AS p2
ON p2."id" = Names."id"
AND (SELECT COUNT(*)
FROM Phones
WHERE "id" = p2."id"
AND primarykey <= p2.primarykey) = 1


try:

LEFT JOIN ( SELECT *
FROM Phones AS p2bis
WHERE p2bis.id=Names.id
AND 1 = (SELECT COUNT(*)
FROM Phones
WHERE p2bis.id = id
AND primarykey <=
p2bis.primarykey)
) As p2





Virtual table are not officially supported in Jet 3.5 or before (Access 97
and older).


Hoping it may help,
Vanderghast, Access MVP