sql 2k view with Union duplicating records

13 Jul 2006 3:54 PM

Ben

Hi

We have a view that appears to be duplicating a record.

We have two tables identical struction apart from one additional field
(which is replaced with a null in the other table within the view). We have
a view that joins the two tables showing all fields, it is a basic union
replacing the single differing field with a null.

We have found that one record is duplicated within the view, although it
only appears once in one table and not at all in the other.

Is there any reason that this could happen?

Thanks
B

13 Jul 2006 4:05 PM

Arnie Rowland

It would be so much easier to attempt to assist you if you would post the
table and view DDL, as well as the query using the UNION. -And a few rows of
sample data in the form of INSERT statements would be excellent.

Otherwise, we are playing twenty questions...

--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."

Show quote

"Ben" <B**@Newsgroups.microsoft.com> wrote in message
news:uhe7xSppGHA.2464@TK2MSFTNGP03.phx.gbl...
> Hi
>
> We have a view that appears to be duplicating a record.
>
> We have two tables identical struction apart from one additional field
> (which is replaced with a null in the other table within the view). We
> have a view that joins the two tables showing all fields, it is a basic
> union replacing the single differing field with a null.
>
> We have found that one record is duplicated within the view, although it
> only appears once in one table and not at all in the other.
>
> Is there any reason that this could happen?
>
> Thanks
> B
>

13 Jul 2006 5:17 PM

Tracy McKibben

Ben wrote:
Show quote

> Hi
>
> We have a view that appears to be duplicating a record.
>
> We have two tables identical struction apart from one additional field
> (which is replaced with a null in the other table within the view). We have
> a view that joins the two tables showing all fields, it is a basic union
> replacing the single differing field with a null.
>
> We have found that one record is duplicated within the view, although it
> only appears once in one table and not at all in the other.
>
> Is there any reason that this could happen?
>
> Thanks
> B
>
>

Sounds like a bad JOIN condition, but without DDL and code, it's
impossible to help you.

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com