Hello,

When I remove MYTABLE5.HAPPYCAPACITY from the SELECT clauses of the
following query, the number of rows returned drops from 28 to 24. If
I put it back in it goes back up to 28. Any idea how this is
possible?

Thanks,
Eric

SELECT
MYTABLE2.ACCOUNTID, MYTABLE2.HostOwnershipType AS COAPPTYPE,
MYTABLE5.HAPPYCAPACITY
FROM MYTABLE1, CUSTOMERS, SPECIALCUSTOMERS MYTABLE2,
SPECIALCUSTOMERS MYTABLE3, MYTABLE5
WHERE MYTABLE1.HAPPY = 'Yes'
AND MYTABLE1.BUDDYNAME = (CUSTOMERS.FIRSTNAME || ' ' ||
CUSTOMERS.LASTNAME)
AND (CUSTOMERS.MIDDLENAME IS NULL OR CUSTOMERS.MIDDLENAME = '')
AND CUSTOMERS.CUSTOMERID = MYTABLE2.CUSTOMERID
AND MYTABLE1.CUSTOMERID = MYTABLE3.CUSTOMERID
AND MYTABLE1.CUSTOMERID = MYTABLE5.CUSTOMERID
UNION
SELECT
MYTABLE2.ACCOUNTID, MYTABLE2.HostOwnershipType AS COAPPTYPE,
MYTABLE5.HAPPYCAPACITY
FROM MYTABLE1, CUSTOMERS, SPECIALCUSTOMERS MYTABLE2,
SPECIALCUSTOMERS MYTABLE3, MYTABLE5
WHERE MYTABLE1.HAPPY = 'Yes'
AND MYTABLE1.BUDDYNAME = (CUSTOMERS.FIRSTNAME || ' ' ||
CUSTOMERS.MIDDLENAME || ' ' || CUSTOMERS.LASTNAME)
AND CUSTOMERS.MIDDLENAME IS NOT NULL AND CUSTOMERS.MIDDLENAME <> ''
AND CUSTOMERS.CUSTOMERID = MYTABLE2.CUSTOMERID
AND MYTABLE1.CUSTOMERID = MYTABLE3.CUSTOMERID
AND MYTABLE1.CUSTOMERID = MYTABLE5.CUSTOMERID;

UNION removes duplicates from the result, try UNION ALL if you want
all rows returned.

Regards,
Miro

Eric Goforth wrote:

> Hello,
>
> When I remove MYTABLE5.HAPPYCAPACITY from the SELECT clauses of the
> following query, the number of rows returned drops from 28 to 24. If
> I put it back in it goes back up to 28. Any idea how this is
> possible?
>
> Thanks,
> Eric
>
> SELECT
> MYTABLE2.ACCOUNTID, MYTABLE2.HostOwnershipType AS COAPPTYPE,
> MYTABLE5.HAPPYCAPACITY
> FROM MYTABLE1, CUSTOMERS, SPECIALCUSTOMERS MYTABLE2,
> SPECIALCUSTOMERS MYTABLE3, MYTABLE5
> WHERE MYTABLE1.HAPPY = 'Yes'
> AND MYTABLE1.BUDDYNAME = (CUSTOMERS.FIRSTNAME || ' ' ||
> CUSTOMERS.LASTNAME)
> AND (CUSTOMERS.MIDDLENAME IS NULL OR CUSTOMERS.MIDDLENAME = '')
> AND CUSTOMERS.CUSTOMERID = MYTABLE2.CUSTOMERID
> AND MYTABLE1.CUSTOMERID = MYTABLE3.CUSTOMERID
> AND MYTABLE1.CUSTOMERID = MYTABLE5.CUSTOMERID
> UNION
> SELECT
> MYTABLE2.ACCOUNTID, MYTABLE2.HostOwnershipType AS COAPPTYPE,
> MYTABLE5.HAPPYCAPACITY
> FROM MYTABLE1, CUSTOMERS, SPECIALCUSTOMERS MYTABLE2,
> SPECIALCUSTOMERS MYTABLE3, MYTABLE5
> WHERE MYTABLE1.HAPPY = 'Yes'
> AND MYTABLE1.BUDDYNAME = (CUSTOMERS.FIRSTNAME || ' ' ||
> CUSTOMERS.MIDDLENAME || ' ' || CUSTOMERS.LASTNAME)
> AND CUSTOMERS.MIDDLENAME IS NOT NULL AND CUSTOMERS.MIDDLENAME <> ''
> AND CUSTOMERS.CUSTOMERID = MYTABLE2.CUSTOMERID
> AND MYTABLE1.CUSTOMERID = MYTABLE3.CUSTOMERID
> AND MYTABLE1.CUSTOMERID = MYTABLE5.CUSTOMERID;