Need Help to Create SQL Statement

[March]

Hi All,

I want to create SQL Statement to get unmatched record(s) from two tables.
Here are the example of the tables:

TB1
===

Field1 Field2 Field3 Field4
==== ===== ===== =====
4/8/2008 AC1 AA A1
4/8/2008 AC2 AA A1
4/8/2008 AC1 BB B1


TB2
===

Field1 Field2 Field3 Field4
==== ===== ===== =====
4/8/2008 AC1 AA A1
4/8/2008 AC2 AA A1
4/8/2008 AC2 BB B1


I could use "INNER JOIN" to get

4/8/2008 AC1 AA A1
4/8/2008 AC2 AA A1

from both tables.


My question is I want to get

4/8/2008 AC1 BB B1 from TB1

AND

4/8/2008 AC2 BB B1 from TB2


by joining both tables.

I know that I need to use "OUTER JOIN", however in Microsoft Access doesn't
support to use the key word. I try "LEFT JOIN" and "RIGHT JOIN" but I could
not get the record(s) I want. Also I use VBA in MS Access to write the code.


Please give me suggestion.

Thank you,

March

 

[Dirk Goldgar]

Hi All,

I want to create SQL Statement to get unmatched record(s) from two tables.
Here are the example of the tables:

TB1
===

Field1 Field2 Field3 Field4
==== ===== ===== =====
4/8/2008 AC1 AA A1
4/8/2008 AC2 AA A1
4/8/2008 AC1 BB B1


TB2
===

Field1 Field2 Field3 Field4
==== ===== ===== =====
4/8/2008 AC1 AA A1
4/8/2008 AC2 AA A1
4/8/2008 AC2 BB B1


I could use "INNER JOIN" to get

4/8/2008 AC1 AA A1
4/8/2008 AC2 AA A1

from both tables.


My question is I want to get

4/8/2008 AC1 BB B1 from TB1

AND

4/8/2008 AC2 BB B1 from TB2


by joining both tables.

I know that I need to use "OUTER JOIN", however in Microsoft Access
doesn't
support to use the key word. I try "LEFT JOIN" and "RIGHT JOIN" but I
could
not get the record(s) I want. Also I use VBA in MS Access to write the
code.


Please give me suggestion.

Thank you,

March

You need to use a UNION operation to merge the results of two separate outer
joins. For example,

SELECT TB1.* FROM
TB1 LEFT JOIN TB2
ON TB1.Field1 = TB2.Field1 AND
TB1.Field2 = TB2.Field2 AND
TB1.Field3 = TB2.Field4 AND
TB1.Field4 = TB2.Field4 AND
WHERE TB2.Field1 Is Null
UNION ALL
SELECT TB2.* FROM
TB1 RIGHT JOIN TB2
ON TB1.Field1 = TB2.Field1 AND
TB1.Field2 = TB2.Field2 AND
TB1.Field3 = TB2.Field4 AND
TB1.Field4 = TB2.Field4 AND
WHERE TB1.Field1 Is Null

That assumes that Field1 will never actually be Null in either table, so you
can safely test for an unmatched record by just checking to see if Field1 is
Null in the joined table.

 

[Ken Sheridan]

You can use a LEFT OUTER JOIN and test for any column on the right side of
the join being NULL, but this is one directional, so what you can do to
return rows from both tables is use a UNION ALL operation:

SELECT TB1.Field1, TB1.Field2, TB1.Field3,TB1.Field4
FROM TB1 LEFT JOIN TB2
ON TB1.Field1 = TB2.Field1
AND TB1.Field2 = TB2.Field2
AND TB1.Field3 = TB2.Field3
AND TB1.Field4 = TB2.Field4
WHERE TB2.Field1 IS NULL
UNION ALL
SELECT TB2.Field1, TB2.Field2, TB2.Field3, TB2.Field4
FROM TB2 LEFT JOIN TB1
ON TB2.Field1 = TB1.Field1
AND TB2.Field2 = TB1.Field2
AND TB2.Field3 = TB1.Field3
AND TB2.Field4 = TB1.Field4
WHERE TB1.Field1 IS NULL;

As you'll see from the above the OUTER is optional, so outer joins are
usually expressed simply as LEFT or RIGHT JOIN.

Ken Sheridan
Stafford, England

 

[March]

Thank you so much. This really help. Try this before, long day today I mess
up all the code.

Thank you again.


March

 

[Wolfgang Kais]

Hello March.

Hi All,

I want to create SQL Statement to get unmatched record(s) from two
tables. Here are the example of the tables:

TB1
===

Field1 Field2 Field3 Field4
==== ===== ===== =====
4/8/2008 AC1 AA A1
4/8/2008 AC2 AA A1
4/8/2008 AC1 BB B1


TB2
===

Field1 Field2 Field3 Field4
==== ===== ===== =====
4/8/2008 AC1 AA A1
4/8/2008 AC2 AA A1
4/8/2008 AC2 BB B1


I could use "INNER JOIN" to get

4/8/2008 AC1 AA A1
4/8/2008 AC2 AA A1

from both tables.


My question is I want to get

4/8/2008 AC1 BB B1 from TB1
AND
4/8/2008 AC2 BB B1 from TB2

by joining both tables.

Are you sure that you want these two records to be returned among
each other? They come from different tables, so you will have to use
a union query for that.

I know that I need to use "OUTER JOIN", however in Microsoft Access
doesn't support to use the key word.

In SQL, an OUTER JOIN always is either a LEFT OUTER JOIN or a RIGHT
OUTER JOIN where one can omit the word OUTER, just like you can omit
the word INNER for an INNER JOIN.

I try "LEFT JOIN" and "RIGHT JOIN" but I could not get the record(s)
I want. Also I use VBA in MS Access to write the code.

A LEFT JOIN will use all records from the left table (the one on the
left of the LEFT JOIN in the FROM clause, and a RIGHT JOIN will use
all records from the right table (the one on the right of the RIGHT
JOIN in the FROM clause.

Please give me suggestion.

As I said, you will have to use a union query. Try one like this:
SELECT TB1.Field1, TB1.Field2, TB1.Field3, TB4.Field4 FROM TB1 LEFT
JOIN TB2 ON (TB1.Field1 = TB2.Field1) AND (TB1.Field2 = TB2.Field2)
AND (TB1.Field3 = TB2.Field3) AND (TB1.Field4 = TB2.Field4)
UNION ALL
SELECT TB2.Field1, TB2.Field2, TB2.Field3, TB2.Field4 FROM TB1 RIGHT
JOIN TB2 ON (TB1.Field1 = TB2.Field1) AND (TB1.Field2 = TB2.Field2)
AND (TB1.Field3 = TB2.Field3) AND (TB1.Field4 = TB2.Field4)

 

[Wolfgang Kais]

Sorry, I forgot the IS NULL criteria, but the others mentioned them.