At 05:22 AM 19/01/2005 +0000, you wrote:

>Thanks Helen this works great.
>I changed the SQl code to the following.
>
>SELECT WPVBSQ , LrgAddrTyp , LrgAddrData
>FROM LargeAddress
>WHERE WPVBSQ IN
> (
> SELECT CrtInfIDSQ
> FROM CreativeInfo
> WHERE (CrtInfName LIKE '%SMITH%' OR CrtInfDescription LIKE '%CAR
>DEALER%'
> )
>GROUP BY WPVBSQ , LrgAddrTyp , LrgAddrData
>
>UNION
>
>SELECT WPVBSQ , SmllAddrTyp , CAST (SmllAddrData as VARCHAR(100))
>FROM SmallAddress
>WHERE WPVBSQ IN
> (
> SELECT CrtInfIDSQ
> FROM CreativeInfo
> WHERE (CrtInfName LIKE '%SMITH%' OR CrtInfDescription LIKE '%CAR
>DEALER%'
> )
>GROUP BY WPVBSQ , SmllAddrTyp , SmllAddrData
>
>Is there a way to group the total results.

No, the logic of UNION is such that you must group within the individual
queries. You can ORDER the entire set though (must order by degree).

>Also I can't use the INTERSECT operator , does it have another name or
>is there another way of doing an intersect.

Not supported. The nearest you will get to it is to define a view
consisting of a UNION ALL, and do a re-entrant full join on the view with
some WHERE clauses to eliminate rows that have nulls on both sides of the
join. Somewhere in there would have to be a DISTINCT or some more outer
join legerdemain to ensure that the final output didn't include duplicate
rows. And no, I'm not volunteering to write an example!! (Actually, it
could be a good one for one of Ivan's brain-teasers! <g>)

>And how does one specify
>the fields at which the INTERSECT must look at to determine its
>results ?

Well, if we *did* support INTERSECT, it's no different from UNION in how
you specify it. What it does though, is it excludes rows *unless* the same
distinct row occurs in all contributing sets, and then outputs one row of
each intersecting row pair.

Actually, I was looking for a bookmark to an article I read not too long
ago about table design. (It might have been a magazine article, though...)
Your situation here is a virtually perfect example of how NOT to maintain
good data integrity. It's just that a bell rang when I noticed your WHERE
clauses and I remembered this article summing up (amongst other things)
that INTERSECT was invented almost exclusively for firefighting bad
database design.

Sorry, I'm not known for my sweetness on this topic. I get irate when
developers get landed with years and years of these sticky, resource-hungry
problems because someone "back then" didn't think "design" was something
one applied to data structures.

./heLen