full join

[Cagliostro]

Hi, how can I make full join in Access? John Fill

ivfmd@mail.md

 

[tlbroadbent]

Add the tables you want to join to the query design window. Don't add any join criteria. If Access automatically adds criteria, remove it.

In SQL View you could do just as in other RDMS.

Example:
SELECT Member.MemberID, Member.MemName, MemberSkill.SkillID
FROM Member, MemberSkill; Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[Cagliostro]

... but how I can specify on which criteria to join? For example I want to join on data, not on ID? John Fill

ivfmd@mail.md

 

[tlbroadbent]

Sorry John, I thought you asked about "full join" which uses no join criteria.

In the Query design window drag the column from one table to the column you want to match in the other table. Or in the SQL view just add the the comparison columns as in the following example.

SELECT tblA.Col1, tblA.Col2, tblA.Col3, tblB.ColA, tblB.ColB
FROM tblA Inner Join tblB
ON tbla.Col2=tblB.ColC;

OR

SELECT tblA.Col1, tblA.Col2, tblA.Col3, tblB.ColA, tblB.ColB
FROM tblA, tblB
WHERE tbla.Col2=tblB.ColC; Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[Cagliostro]

but it is inner join. I need full join as in SQL Server, same thing as left join + right join. John Fill

ivfmd@mail.md

 

[tlbroadbent]

Sorry John, my thought processes are slow today.

There is no FULL OUTER JOIN in Access but you can accomplish the same thing with a combination of Left and Right Outer Joins in a Union query.

SELECT tblA.Col1, tblA.Col2, tblA.Col3, tblB.ColA, tblB.ColB
FROM tblA Left Join tblB
ON tbla.Col2=tblB.ColC
Union
SELECT tblA.Col1, tblA.Col2, tblA.Col3, tblB.ColA, tblB.ColB
FROM tblA Right Join tblB
ON tbla.Col2=tblB.ColC; Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[Cagliostro]

Thanks, but I tried already that:

SELECT tblA.Col1, tblA.Col2, tblA.Col3, tblB.ColA, tblB.ColB
FROM tblA Left Join tblB
ON tbla.Col2=tblB.ColC
where tblB.colC is null
Union
SELECT tblA.Col1, tblA.Col2, tblA.Col3, tblB.ColA, tblB.ColB
FROM tblA Right Join tblB
ON tbla.Col2=tblB.ColC
where tbla.col2 is null
Union
SELECT tblA.Col1, tblA.Col2, tblA.Col3, tblB.ColA, tblB.ColB
FROM tblA inner Join tblB
ON tbla.Col2=tblB.ColC John Fill

ivfmd@mail.md

 

[tlbroadbent]

John,

What result did you get when you tried the Left, Right Join in the Union query? I just did the same thing in SQL server and got the same result with FULL OUTER JOIN, my suggested query and the one you posted. My tests in Access returned the correct result also. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[Cagliostro]

I thought what there are some nicer methods than that. Comparative with SQLServer access is really poor... John Fill

ivfmd@mail.md