Can Access to "FULL OUTER JOINS"?

  • Thread starter Thread starter Conan Kelly
  • Start date Start date
C

Conan Kelly

Hello all,

In SQL Server, I can do FULL OUTER JOINS where I can return all records from both tables/queries. It doesn't appear that Access is
capable of doing this. Can this be done?

TIA,

Conan Kelly
 
Conan Kelly said:
In SQL Server, I can do FULL OUTER JOINS where I can return all records
from both tables/queries. It doesn't appear that Access is capable of
doing this. Can this be done?
Click to expand...
You can accomplish that with

Select ... LEFT JOIN...
UNION
Select....RIGHT JOIN...

but, as Michel once sagely pointed out,
if you need a full outer join, it usually means
you are missing a table in your design.
 
Gary,

Thank you for your feedback.

What you suggested will not accomplish what I'm trying to do. I don't believe how I'm using the FULL OUTER JOINS is a design flaw.

What I use them for is when we do updates, I like to take the "ProductCode" field/column from the exsisting data and same
field/column in the new data (before importing into the main table), line them up next to each other, and do a FULL OUTER JOIN on
them so I can see all the records in both tables (including NULLS in the old data and in the new data). That way I can see what
product codes are new and what product codes have been discontinued.

With your UNION query, I will get all product codes in one column/field. It won't be in the format that I want.

Thanks again for all of your help,

Conan
 
maybe easier to see this way....
sure sounds like what you are describing
(but I could be wrong)

SELECT
t1.ProductCode As Cur,
t2ProductCode As Nw
FROM
currenttable As t1
LEFT JOIN
newtable As t2
ON
t1.ProductCode = t2.ProductCode

UNION

SELECT
t1.ProductCode,
t2ProductCode
FROM
newtable As t2
LEFT JOIN
currenttable As t1
ON
t2.ProductCode = t2.ProductCode

(of course faster using UNION ALL)

in first getting all of current
and matching new

in second getting all of new
and matching current

The missing table is a table of all
ProductCodes, which you could
left join to new and current to get
this result w/o the UNION.

Sometimes that is not possible,
and that may your case....
I live with several dbs that get
data from sources I have no control
over, so I know it is not a perfect
world, and you do your best with
what you got.....
 
SELECT
t1.ProductCode As Cur,
t2ProductCode As Nw
FROM
currenttable As t1
LEFT JOIN
newtable As t2
ON
t1.ProductCode = t2.ProductCode

UNION

SELECT
t1.ProductCode,
t2ProductCode
FROM
newtable As t2
LEFT JOIN
currenttable As t1
ON
t2.ProductCode = t1.ProductCode

"Gary Walter" crappily typed:
 
Ah-haaaaa!!!!

I see what you are getting at now. Yeah, that should work.

Thanks again for all of your help,

Conan Kelly
 
Hi,


If you have a table with all ProductCode, old and new:


SELECT ProductCode FROM tableOld
UNION
SELECT ProductCode FROM tableNew


Then you would not need full outer join, just simple outer join. Let the
previous query be a table (so we can index its unique field)


SELECT whatever
FROM (previousTable As a LEFT JOIN tableOld as o ON a.productCode =
o.productCode)
LEFT JOIN tableNew As n ON a.productCode = n.productCode


Furthermore, keeping that table with all the possible ProductCode is
probably a good idea, since it is likely it would be involved in other
queries too. Sure, you can also keep it as a UNION query, but then, it is
not indexed.


Vanderghast, Access MVP
 
Back
Top