Num query fields affects num rows

[Eric Goforth]

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;

[miro flasza]

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;