displaying records between tables when one table doesn't have a corresponding...

  • Thread starter Thread starter dan
  • Start date Start date
D

dan

I don't know how to succinctly describe the problem enough
to fit it in the subject line. This is likely an easy
problem asked a hundred of times before. Here is the 101st.

I have a table with two key fields. Subject and date. It
has records for varying dates of subject A, B, C, D, E.
ie, each subject has any number of dates associated with it.

The other table has two key fields. One of which is the
subject and the other is a list of numbers. Not every
subject from table 1 has made it to table 2. Table 2, for
example may only have A, C, D. Here's the problem. I have
(in a query) a list of all the subject from table 1 with
their most recent date. I also need the last number for
each subject in table 2 without it removing the records for
B and E. I don't want to have to create the records in
table 2 (ex. B and a zero). But I'd like a zero or a
blank to show up in the query.

The query result would look like this...

A feb 12 10
B Mar 4
C Mar 11 8
D feb 27 18
E Feb 25

To reiterate, my query is removing records for B and E.


I'm not sure how clear that was. Any and all help is
appreciated.

Thanks in advance,
dan
 
If I understand correctly, you need to change to
an "outer join". If you look at your SQL, it probably
says "INNER JOIN" or just "JOIN". Change that to "LEFT
JOIN" (perhaps RIGHT join depending on how your tables
are ordered) and you'll get all the records in table 1
whether or not the record exists in table 2. If you're
using the visual query interface - just double-click on
the line joining the two tables and select the
appropriate type of join as described by the bullets 1,2,
or 3. The line changes to an arrow indicating which
table takes precedence.
 
Thanks, it was as simple as that.
-----Original Message-----
If I understand correctly, you need to change to
an "outer join". If you look at your SQL, it probably
says "INNER JOIN" or just "JOIN". Change that to "LEFT
JOIN" (perhaps RIGHT join depending on how your tables
are ordered) and you'll get all the records in table 1
whether or not the record exists in table 2. If you're
using the visual query interface - just double-click on
the line joining the two tables and select the
appropriate type of join as described by the bullets 1,2,
or 3. The line changes to an arrow indicating which
table takes precedence.


.
Click to expand...
 
Back
Top