Hi
You could try
CREATE CURSOR T1 (aa c(1),bb i, cc c(1) null)
INSERT INTO T1 VALUES ('a',1,'x')
INSERT INTO T1 VALUES ('b',2,'y')
INSERT INTO T1 VALUES ('c',3,'z')
CREATE CURSOR T2 (aa c(1),bb i)
INSERT INTO T2 VALUES ('a',1)
INSERT INTO T2 VALUES ('b',2)
INSERT INTO T2 VALUES ('c',3)
INSERT INTO T2 VALUES ('d',4)
SELECT aa,bb,cc FROM T1 UNION ;
SELECT T2.aa,T2.bb, ' ' FROM T2 LEFT JOIN T1 ;
ON T2.aa=T1.aa AND T2.bb=T1.bb WHERE T1.aa IS NULL
or
SELECT T2.aa, T2.bb, T1.cc FROM T2 LEFT JOIN T1 ;
ON T1.aa=T1.aa AND T2.bb=T1.bb ;
UNION SELECT * from T1
The last one even solves the NULL problem.
-Anders
----- Original Message -----
From: "Paul Stearns" <pauls (AT) compuace DOT com>
To: "ProFox Email List" <profox@leafe.com>
Sent: Sunday, August 31, 2003 12:58 AM
Subject: Re: Union and select distinct...
> Anders:
>
> Thanks for clearing that up, I wasn't sure where you were going, which
> is why I posed the two table question.
>
> OK, now that I know what you're talking about how would an outer join
> help? Lets simplify things a bit;
>
> I have two tables (sound familiar?);
>
> T1.aa, Data a,b,c
> T1.bb, Data 1,2,3
> T1.cc Data x,y,z
>
> T2.aa, Data a,b,c,d
> T2.bb Data 1,2,3,4
>
> My output set needs to be;
>
> a,1,x
> b,2,y
> c,3,z
> d,4,null
>
> My solution is;
>
> Select T1.aa, T1.bb, T1.cc from T1;
> union;
> Select T2.aa, T2.bb, null from T2;
> Where not exists (select T1.aa from T1 where T1.aa = T2.aa and T1.bb =
> T2.bb)
>
> If there is a better way I would like to know as this method is a bit
> slow. I have tenss of thousands of records in both tables. I have decent
> indexes, but I will recheck this.
>
> Paul
>
>
> Anders Altberg wrote:
>
> >That is a straight UNUIN or UNION ALL case.
> >What I was thinking about is the big query with 35 or more columns from
some
> >7 different tables.The first part of the query is an inner join of the 7
> >tables
> >FROM Maindata, facility, violati, area, workflow, firestn,
fireinsp,inspectr
> >The second part of the UNION seems to pick up rows that the inner join
> >misses by using WHERE NOT EXIST subquery
> >FROM fac_bldg_room,facility,area,workflow,firestn, fireinsp, inspectr;
> >The WHERE NOT EXISTS could possibly be replaced with outer joins. You
would
> >still need a UNION though as the leftmost table is different in the two
> >SELECTs; it's Maindata in the first, then Fac_bldg_room in the second
> >statement.
> >-Anders
> >
> >
> >----- Original Message -----
> >From: "Paul Stearns" <pauls (AT) compuace DOT com>
> >To: <profox@leafe.com>
> >Sent: Friday, August 29, 2003 9:37 PM
> >Subject: Re: Union and select distinct...
> >
> >
> >
> >
> >>Anders:
> >>
> >>I have two tables which contain the fields;
> >>
> >>table1.aa
> >>table1.bb
> >>table1.cc
> >>
> >>table2.aa
> >>table2.bb
> >>table2.cc
> >>
> >>I would like a result set that looks like;
> >>
> >>aa,bb,cc
> >>
> >>and includes data from both tables. I am unclear how I would do this
with
> >>
> >>
> >an
> >
> >
> >>outer join. I could get;
> >>
> >>t1.aa,t1.bb,t1.cc,t2.aa,t2.bb,t2.cc
> >>
> >>with the fields being null from table that didn't populate that row.
> >>
> >>This is obviously a simplified example, but if you know of another way
to
> >>
> >>
> >get
> >
> >
> >>the results I desire I am like Prince Charles (all ears).
> >>
> >>Paul
> >>
> >>
> >>--- "Anders Altberg" <pragma (AT) telia DOT com> wrote:
> >>
> >>
> >>>Still looks like an outer join thing to me <g>.
> >>>-Anders
> >>>
> >>>----- Original Message -----
> >>>From: "Paul Stearns" <pauls@compuace.com>
> >>>To: "ProFox Email List" <profox (AT) leafe DOT com>
> >>>Sent: Thursday, August 28, 2003 8:10 PM
> >>>Subject: RE: Union and select distinct...
> >>>
> >>>
> >>>
> >>>
> >>>>In case anyone was wondering, I was able to find the solution.
> >>>>
> >>>>The problem I was having is a complex query with a union and a select
> >>>>distinct would work without the select distinct, but not with the
> >>>>
> >>>>
> >>distinct
> >>
> >>
> >>>>clause (selects were not compatible in union). It was suggested on the
> >>>>
> >>>>
> >>>list
> >>>
> >>>
> >>>>that I had incompatible field sizes. I tried to manually correct the
> >>>>
> >>>>
> >>sizes
> >>
> >>
> >>>>to no avail, there are about 38 fields in the query.
> >>>>
> >>>>The problem ended up being that one of the character fields was
> >>>>
> >>>>
> >assuming
> >
> >
> >>a
> >>
> >>
> >>>>10 character length in the second query, and a four character length
> >>>>
> >>>>
> >in
> >
> >
> >>>the
> >>>
> >>>
> >>>>first query. Since this was a default behavior it was virtually
> >>>>
> >>>>
> >>impossible
> >>
> >>
> >>>>to see.
> >>>>
> >>>>The way I was able to identify the problem was to split the query into
> >>>>
> >>>>
> >>two
> >>
> >>
> >>>>queries and use a display structure to file on each one. I then fired
> >>>>
> >>>>
> >up
> >
> >
> >>>vim
> >>>
> >>>
> >>>>on each file set nowrap and compared each field in one file against
> >>>>
> >>>>
> >the
> >
> >
> >>>>other.
> >>>>
> >>>>So if anyone gets into this mess remember that display structure is
> >>>>
> >>>>
> >your
> >
> >
> >>>>friend.
> >>>>
> >>>>Paul
> >>>>
> >>>>
> >>>>
> >>>>
[excessive quoting removed by server]
©2003 Anders Altberg |
|
