Description:
When running the following queries:
DROP TABLE IF EXISTS rmallaccountinfo;
---BEGIN QUERIES---
CREATE TEMPORARY TABLE rmallaccountinfo SELECT c.accno, c.pid, u.unit, u.unittype, c.unitid, c.status, c.depositdate, c.astreet1, c.astreet2, c.acity, c.astate, c.azip, c.pstreet1, c.pstreet2, c.pcity, c.pstate, c.pzip, c.movein, c.moveout, c.leasest, c.leasend, c.deposit, c.display, c.active, c.weekly, c.archive, c.rdue, c.stoppay, c.stoplate, c.stopstatement, c.comment AS custcomment, n.fname, n.lname, n.bdate, n.ssn, n.hphone, n.pphone, n.wphone, n.fax, n.carphone, n.altphone, n.email, n.pager, n.comments AS namecomments, n.license, n.car, n.pictfile, n.isprimary, n.showonbill, n.employer, n.iscompany, u.street1 AS unitstreet1, u.street2 AS unitstreet2, u.city AS unitcity, u.state AS unitstate, u.zip AS unitzip, u.comments AS unitcomments, u.sqft, u.meterid, t.typeid, t.descrip AS typedescrip, p.name, p.shortname, p.rctype FROM (((cust AS c LEFT JOIN custnames AS n ON c.accno=n.accno) LEFT JOIN unit AS u ON c.unitid=u.uniquerecid) LEFT JOIN unittype AS t ON u.unittype=t.id) LEFT JOIN prop AS p ON c.pid=p.pid ORDER BY n.lname, n.fname;
ALTER TABLE rmallaccountinfo ADD INDEX rminfo_acc (accno);
SELECT DISTINCT i.itemid, i.descrip
FROM rmAllAccountInfo c, auto a, items i
WHERE c.isprimary<>0 AND c.pid IN (5) AND c.movein<='2003-10-29' AND (c.moveout>='2003-10-29' OR c.moveout IS NULL) AND (c.accno=a.accno OR c.pid=a.pid OR c.unitid=a.unitid OR c.unittype=a.unittype) AND a.itemid=i.itemid
ORDER BY i.itemid;
----End queries----
---Result---
+--------+-------------------------------+
| itemid | descrip |
+--------+-------------------------------+
| AN | Annual RV Rental |
| EU | Employee Unit |
| PM | Park Model |
| RP | Rent Promotion/Concession |
| RV | Short Term Rv Rental |
| ST | Storage |
| T1 | RV Residential Tax |
| T2 | Storage Commercial Tax Charge |
| T3 | SW Residential Tax |
| T4 | DW Residential Tax |
| T5 | Short Term RV Tax |
+--------+-------------------------------+
---END RESULT---
The last SELECT DISTINCT query loses one of the distinct combinations (RC,Rent Charge) of i.itemid, i.descrip. However if GROUP BY is used instead as in
SELECT i.itemid, i.descrip
FROM rmAllAccountInfo c, auto a, items i
WHERE c.isprimary<>0 AND c.pid IN (5) AND c.movein<='2003-10-29' AND (c.moveout>='2003-10-29' OR c.moveout IS NULL) AND (c.accno=a.accno OR c.pid=a.pid OR c.unitid=a.unitid OR c.unittype=a.unittype) AND a.itemid=i.itemid
GROUP BY i.itemid, i.descrip
ORDER BY i.itemid;
The result is correct:
+--------+-------------------------------+
| itemid | descrip |
+--------+-------------------------------+
| AN | Annual RV Rental |
| EU | Employee Unit |
| PM | Park Model |
| RC | Rent Charge | <--- this is the one missing with
| RP | Rent Promotion/Concession | the SELECT DISTINCT
| RV | Short Term Rv Rental |
| ST | Storage |
| T1 | RV Residential Tax |
| T2 | Storage Commercial Tax Charge |
| T3 | SW Residential Tax |
| T4 | DW Residential Tax |
| T5 | Short Term RV Tax |
+--------+-------------------------------+
---
If the DISTINCT keyword is removed then the RC,Rent Charge combination shows up in the list.
How to repeat:
Stated in the description of the problem.