Duplicate record dropped in SQL UNION query...

Hi --
-
I have 8 tables in a directory. One of the tables has a duplicate
record. The number of records for all 8 tables, including the
duplicate record, is 13,021
-
I joined the 8 tables using SQL UNION. One of the instances of the
duplicate record is NOT part of the resulting cursor. The total number
of records in the cursor is 13,020.
-
How did this happen?
-
Thanks for any help.
David

Mark
Mon Aug 07 02:09:51 CDT 2006

that is because union is by default distinct. you need to say 'union all' to
get the behaviour you are asking for.
"David" <David.Aman@dpsnc.net> wrote in message
news:1154918374.944418.45950@b28g2000cwb.googlegroups.com...
> Hi --
> -
> I have 8 tables in a directory. One of the tables has a duplicate
> record. The number of records for all 8 tables, including the
> duplicate record, is 13,021
> -
> I joined the 8 tables using SQL UNION. One of the instances of the
> duplicate record is NOT part of the resulting cursor. The total number
> of records in the cursor is 13,020.
> -
> How did this happen?
> -
> Thanks for any help.
> David
>

Man-wai
Mon Aug 07 06:39:27 CDT 2006

Mark Burgum wrote:
> that is because union is by default distinct. you need to say 'union all' to

Is the ANSI SQL standard using the same definition?

--
.~. Might, Courage, Vision, SINCERITY. http://www.linux-sxs.org
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Ubuntu 6.06) Linux 2.6.17.8
^ ^ 19:38:01 up 34 min 0 users load average: 1.55 1.23 1.20
news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

Mark
Mon Aug 07 07:31:53 CDT 2006

i have to confess i have never looked at the Ansi Sql. i do know that this
behaviour for foxpro sql goes back to version 2.6 through to vfp 8, i
haven't checked 9, as we are not running that here yet.

"Man-wai Chang" <toylet.toylet@gmail.com> wrote in message
news:umkqjYhuGHA.736@TK2MSFTNGP02.phx.gbl...
> Mark Burgum wrote:
>> that is because union is by default distinct. you need to say 'union all'
>> to
>
> Is the ANSI SQL standard using the same definition?
>
> --
> .~. Might, Courage, Vision, SINCERITY. http://www.linux-sxs.org
> / v \ Simplicity is Beauty! May the Force and Farce be with you!
> /( _ )\ (Ubuntu 6.06) Linux 2.6.17.8
> ^ ^ 19:38:01 up 34 min 0 users load average: 1.55 1.23 1.20
> news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

David
Mon Aug 07 08:14:19 CDT 2006

Thanks for your help, Mark! I searched for help on this issue using
F1, but was unsuccessful. Now I know!
-
David
===========================
Mark Burgum wrote:
> that is because union is by default distinct. you need to say 'union all' to
> get the behaviour you are asking for.
> "David" <David.Aman@dpsnc.net> wrote in message
> news:1154918374.944418.45950@b28g2000cwb.googlegroups.com...
> > Hi --
> > -
> > I have 8 tables in a directory. One of the tables has a duplicate
> > record. The number of records for all 8 tables, including the
> > duplicate record, is 13,021
> > -
> > I joined the 8 tables using SQL UNION. One of the instances of the
> > duplicate record is NOT part of the resulting cursor. The total number
> > of records in the cursor is 13,020.
> > -
> > How did this happen?
> > -
> > Thanks for any help.
> > David
> >

Olaf
Mon Aug 07 11:48:33 CDT 2006

> Thanks for your help, Mark! I searched for help on this issue using
> F1, but was unsuccessful. Now I know!

The help on the Select - SQL command says so in the paragraph
about the Union Clause. Or in the newer VFP Versions there is
a seperate chapter on the union clause that says so.

[UNION [ALL] SELECTCommand]
Specifies another SELECT statement. By default, UNION eliminates
duplicate rows from the combined result set.

Note
The ALL keyword includes duplicate rows in the combined result set.



Bye, Olaf.


begin 666 note.gif
M1TE&.#EA"@`*`+,)`/\$`/]=6L;#QM;3UO_/`(2&A(R.C ```/__SO___P``
M`````````````````````"'Y! $```D`+ `````*``H```0HT,@Y1 `&Z7T(
AWEIW)!E'C.16G$.2(L-1((5;%D-H@_H+HH>@,.B*```[
`
end

AA
Sat Aug 12 07:53:27 CDT 2006

Yes, that is an ANSI/ISO SQL standard requirement.
-Anders

"Man-wai Chang" <toylet.toylet@gmail.com> skrev i meddelandet
news:umkqjYhuGHA.736@TK2MSFTNGP02.phx.gbl...
> Mark Burgum wrote:
>> that is because union is by default distinct. you need to say 'union all'
>> to
>
> Is the ANSI SQL standard using the same definition?
>
> --
> .~. Might, Courage, Vision, SINCERITY. http://www.linux-sxs.org
> / v \ Simplicity is Beauty! May the Force and Farce be with you!
> /( _ )\ (Ubuntu 6.06) Linux 2.6.17.8
> ^ ^ 19:38:01 up 34 min 0 users load average: 1.55 1.23 1.20
> news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

Man-wai
Sat Aug 12 09:12:11 CDT 2006

AA wrote:
> Yes, that is an ANSI/ISO SQL standard requirement.
> -Anders

Thanks. I am learning and picking up ...wait for me... :)

--
.~. Might, Courage, Vision, SINCERITY. http://www.linux-sxs.org
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Ubuntu 6.06) Linux 2.6.17.8
^ ^ 22:11:01 up 5 days 3:07 0 users load average: 1.07 1.04 1.01
news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk