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 Paul Stearns |
|
