unwanted duplicate loan account numbers

[Guest]

hi,

I have the following make_history query:

SELECT
tbl_masterpop_new.[Loan Acct #], tbl_masterpop_new.Status,
tbl_masterpop_new.PopEnterDt, tbl_masterpop_new.[chg off date],
tbl_masterpop_new.[loan status], tbl_masterpop_new.[inventory status],
tbl_masterpop_new.[active bk flag], tbl_masterpop_new.[active inactive flag],
tbl_masterpop_new.[paid off?], tbl_masterpop_new.[payoff date],
tbl_masterpop_new.[days delinquent], tbl_masterpop_new.[amended this mo?],
tbl_masterpop_new.[extended this mo?]
FROM tbl_masterpop_new LEFT JOIN tbl_masterpop_main
ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop_main.[Loan Acct #]
WHERE tbl_masterpop_main.[Loan Acct #] is Null
UNION ALL SELECT
tbl_masterpop.[Loan Acct #], tbl_masterpop.Status, tbl_masterpop.PopEnterDt,
tbl_masterpop.[chg off date], tbl_masterpop.[loan status],
tbl_masterpop.[inventory status], tbl_masterpop.[active bk flag],
tbl_masterpop.[active inactive flag], tbl_masterpop.[paid off?],
tbl_masterpop.[payoff date], tbl_masterpop.[days delinquent],
tbl_masterpop.[amended this mo?], tbl_masterpop.[extended this mo?]
FROM tbl_masterpop LEFT JOIN tbl_masterpop_main
ON tbl_masterpop.[Loan Acct #] = tbl_masterpop_main.[Loan Acct #]
WHERE tbl_masterpop_main.[Loan Acct #] is Null;

When I look at the results, I notice that there are duplicate [loan acct #]
(2 in fact) even though the records have different [status]. I DO NOT want a
[loan account #] to be listed twice in the query. How can I amend the query
to do this?

thanks in advance,
geebee

 

[Tom Ellison]

Dear GB:

I have rewritten your query a bit just to help me study it:

SELECT N.[Loan Acct #], N.Status, N.PopEnterDt, N.[chg off date],
N.[loan status], N.[inventory status], N.[active bk flag], N.[active
inactive flag],
N.[paid off?], N.[payoff date], N.[days delinquent], N.[amended this
mo?],
N.[extended this mo?]
FROM tbl_masterpop_new N
LEFT JOIN tbl_masterpop_main M
ON N.[Loan Acct #] = M.[Loan Acct #]
WHERE M.[Loan Acct #] is Null

UNION ALL

SELECT P.[Loan Acct #], P.Status, P.PopEnterDt, P.[chg off date],
P.[loan status], P.[inventory status], P.[active bk flag], P.[active
inactive flag],
P.[paid off?], P.[payoff date], P.[days delinquent], P.[amended this
mo?],
P.[extended this mo?]
FROM tbl_masterpop P
LEFT JOIN tbl_masterpop_main M
ON P.[Loan Acct #] = M.[Loan Acct #]
WHERE M.[Loan Acct #] is Null;

Now, you say there is a duplicated [loan acct #] in this query's results.
It would be useful to find out from which side of the UNION this came.
Whether it is for debugging, or is often also useful in the production based
on this query, I usually track the SOURCE of each row. I would modify it as
follows:

SELECT "N" AS Source,
N.[Loan Acct #], N.Status, N.PopEnterDt, N.[chg off date],
N.[loan status], N.[inventory status], N.[active bk flag], N.[active
inactive flag],
N.[paid off?], N.[payoff date], N.[days delinquent], N.[amended this
mo?],
N.[extended this mo?]
FROM tbl_masterpop_new N
LEFT JOIN tbl_masterpop_main M
ON N.[Loan Acct #] = M.[Loan Acct #]
WHERE M.[Loan Acct #] is Null

UNION ALL

SELECT "P" AS Source,
P.[Loan Acct #], P.Status, P.PopEnterDt, P.[chg off date],
P.[loan status], P.[inventory status], P.[active bk flag], P.[active
inactive flag],
P.[paid off?], P.[payoff date], P.[days delinquent], P.[amended this
mo?],
P.[extended this mo?]
FROM tbl_masterpop P
LEFT JOIN tbl_masterpop_main M
ON P.[Loan Acct #] = M.[Loan Acct #]
WHERE M.[Loan Acct #] is Null;

ORDER BY [Loan Acct #]

Now every row is labelled as to its source. I have added sorting so the
duplicates come together in the results. Using this, what are the sources
of the duplicate rows? Were there two rows in tbl_masterpop_new with the
same [Loan Acct #], or two rows in tbl_masterpop with the same [Loan Acct
#], or one from each table?

Now, if you have such a duplicate in one column, the other columns may
differ between the two rows. If you have two, or more, rows with the same
[Loan Acct #], and any of the other columns differ, then by definition if
you eliminate the duplicate [Loan Acct #] you will have to show only a
single value for each of the other columns. On what basis do you want to
choose which one to see? You might want to see the one from
tbl_masterpop_new. You might want to see a particular Status over another,
but the Status values might be the same.

The points are:

- you must choose a basis or set of bases for choosing what to show.

- you will lose invormation. If the two are different in other columns, and
you show one over the other, then the one you eliminate could contain
information that will be suppressed.

If a way could be found to do this arbitrarily, choosing one over the other,
I'm not so sure that would be a good idea. It is normally best for you to
control exactly how your queries work. Indeed, the way queries work is
purposely designed to prevent ambiguity or unpredictable results.
Normally, this is a very good thing.

Tom Ellison

hi,

I have the following make_history query:

SELECT
tbl_masterpop_new.[Loan Acct #], tbl_masterpop_new.Status,
tbl_masterpop_new.PopEnterDt, tbl_masterpop_new.[chg off date],
tbl_masterpop_new.[loan status], tbl_masterpop_new.[inventory status],
tbl_masterpop_new.[active bk flag], tbl_masterpop_new.[active inactive
flag],
tbl_masterpop_new.[paid off?], tbl_masterpop_new.[payoff date],
tbl_masterpop_new.[days delinquent], tbl_masterpop_new.[amended this mo?],
tbl_masterpop_new.[extended this mo?]
FROM tbl_masterpop_new LEFT JOIN tbl_masterpop_main
ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop_main.[Loan Acct #]
WHERE tbl_masterpop_main.[Loan Acct #] is Null
UNION ALL SELECT
tbl_masterpop.[Loan Acct #], tbl_masterpop.Status,
tbl_masterpop.PopEnterDt,
tbl_masterpop.[chg off date], tbl_masterpop.[loan status],
tbl_masterpop.[inventory status], tbl_masterpop.[active bk flag],
tbl_masterpop.[active inactive flag], tbl_masterpop.[paid off?],
tbl_masterpop.[payoff date], tbl_masterpop.[days delinquent],
tbl_masterpop.[amended this mo?], tbl_masterpop.[extended this mo?]
FROM tbl_masterpop LEFT JOIN tbl_masterpop_main
ON tbl_masterpop.[Loan Acct #] = tbl_masterpop_main.[Loan Acct #]
WHERE tbl_masterpop_main.[Loan Acct #] is Null;

When I look at the results, I notice that there are duplicate [loan acct
#]
(2 in fact) even though the records have different [status]. I DO NOT
want a
[loan account #] to be listed twice in the query. How can I amend the
query
to do this?

thanks in advance,
geebee