Join Query

[Phil]

Hi All,

I have 2 tables that look like the following

ID, SDate, EDate, OIN
1 071105 081105 123
1 071105 081105 456

ID, SDate, EDate, OIN
1 071105 081105 123
1 071105 081105 456
1 071105 081105 789

What I want is by joining on the is pull back the records that are in the
secod table and are not in the first, so I want my query to come back with
the results

ID, SDate, EDate, OIN
1 071105 081105 789

I have tried various joins and outer joins but not with my luck, another fly
in the ointment perhaps is all these fields are text and need to stay that
way so I cant change any of the table designs.

If anyone knows of a solution to this it would be much appreciated.

Thanks

 

[Tom Ellison]

Dear Phil:

So, given the data you show, the row with OIN = 789 is in the second table
but not in the first. So you want to "find" this row. Is it "missing"
based solely on the OIN column value, on all the values?

You would need to JOIN on all the columns necessary for matching rows
between the tables. Here's a sample for just the OIN column:

SELECT *
FROM Table1
RIGHT JOIN Table2
ON Table2.OIN = Table1.OIN
WHERE Table1.OIN IS NULL

The RIGHT JOIN says to include every row in Table2 whether there's a match
in Table1 or not. This would give you 3 rows. The filter removes rows
where the OIN in the matching Table1 is showing NULL. Assuming OIN is never
null, that would happen only when no match was found.

Please notice the assumption I have made about OIN being NULL. That's
critical. If that's not the case, but another column, say ID, is never NULL
then change the test to use that column.

In a LEFT or RIGHT JOIN, the table on the "far" side of the JOIN will show
all columns NULL when there's no record there. That makes it a good way to
accomplish what you want here.

Please let me know if this helped and if I can be of any other assistance.

Tom Ellison

 

[Phil]

Hi Tom,

Thanks again for your great advice, I have tried running your query and it
doesn't quite bring back the results that I Exspected, the OIN number is
never null, it's just a case you have one whole record that is in one table
and not in another, if that makes any sense,

 

[Phil]

I think I might have got my statement around the wrong way, I have now
changed it and I seem to have more rows which I have checked manually and
they should be showing in the query, although I have noticed that not all the
ones that should be showing in the query are and there is nothing that makes
these different from the ones that it will show, is there any reason why this
might be.

Thanks Phil

 

[Douglas J. Steele]

Have you tried using the Find Unmatched Query wizard that's available when
you go to create a new query?

 

[Michel Walsh]

Hi,


You will also have to manually edit the result to make a multiple join.
Drag and drop each field from table2 to the corresponding field in table1.
Edit all these "lines" to make them keeping all records from table2. The
final result (may be faster to type the SQL statement from scratch) would
look like:
======================

SELECT table2.*

FROM table2 LEFT JOIN table1
ON table2.ID=table1.ID
AND table2.Sdate = table1.Sdate
AND table2.Edate = table1.Edate
AND table2.oin =table1.oin

WHERE table1.id IS NULL

========================

Note that since the JOIN is apply BEFORE the WHERE clause, table1.id
entries, after the join, may have NULL... in case there is no match as
defined in the ON criteria. That is exactly those unmatched record,
initially in Table2 but not in table1, that you said you wanted.



Hoping it may help,
Vanderghast, Access MVP

 

[Phil]

Just wanted to say a quick thanks to everyone on her that has answered my
many questions!!..

Thanks!