MySQL – Selecting Records with Zero Dues

MySQLrowselect

DUES_TABLE
ID       LNAME   FNAME   DUES    DATE
3456     Smith   Bob     12.50   2014-10-01
3456     Smith   Bob      0.00   2014-11-01
3456     Smith   Bob     12.50   2014-12-01

9450     Jones   Jane    12.50   2014-10-01
9450     Jones   Jane    12.50   2014-11-01
9450     Jones   Jane    12.50   2014-12-01

6074     Suggs   Tom     12.50   2014-10-01
6074     Suggs   Tom      0.00   2014-11-01
6074     Suggs   Tom     12.50   2014-12-01

I want to output all records, whether dues were paid or not, for those persons who have one or more zero dues entries, like so:

3456     Smith   Bob     12.50   2014-10-01
3456     Smith   Bob      0.00   2014-11-01
3456     Smith   Bob     12.50   2014-12-01
9450     Suggs   Tom     12.50   2014-10-01
9450     Suggs   Tom      0.00   2014-11-01
9450     Suggs   Tom     12.50   2014-12-01

Notice that Jane Jones is excluded because she paid all her dues. And Both Bob Smith and Tom Suggs have their zero and positive dues entries listed.

Anyone have an idea how I can achieve this result?

Best Answer

SELECT DISTINCT ID FROM tbl WHERE DUES = 0

will get the IDs of the naughty people, correct? So now you need to do a "self-JOIN" back to get the rest of the data:

SELECT DISTINCT a.*
    FROM tbl AS a
    JOIN tbl AS b  USING(ID)
    WHERE b.DUES = 0
    ORDER BY a.ID, a.DATE

Or...

SELECT *
    FROM tbl AS a
    WHERE EXISTS ( SELECT ID FROM tbl
                      WHERE DUES = 0
                      AND ID = a.ID
                 )
    ORDER BY ID, DATE

Related Solutions

Select Records Without Associations Outside a Certain List in MySQL

Here is the query

SELECT C.* FROM
(SELECT nid,COUNT(1) fullcount 
  FROM node 
  INNER JOIN field_data_dir_phys_category   AS category   ON node.nid = category.entity_id
  GROUP BY nid) A INNER JOIN
(SELECT nid,COUNT(1) goodcount 
  FROM node
  INNER JOIN field_data_dir_phys_category   AS category   ON node.nid = category.entity_id
  WHERE dir_phys_category_tid IN (498,499) GROUP BY nid) B USING (nid)
INNER JOIN (SELECT DISTINCT nid,title FROM node) C USING (nid)
WHERE fullcount=goodcount;

First, let's make some sample data. NOTE: this uses a single table. The OP used two tables - mine is intended to demonstrate the method used to count categories, which is the central part of my answer.

DROP DATABASE IF EXISTS doub1ejack;
CREATE DATABASE doub1ejack;
USE doub1ejack
CREATE TABLE node
(
    id INT NOT NULL AUTO_INCREMENT,
    nid INT NOT NULL,
    title VARCHAR(20),
    dir_phys_category_tid INT NOT NULL,
    PRIMARY KEY (id)
);
INSERT INTO node (nid,title,dir_phys_category_tid) VALUES
(1171,'Toby',499),(1172,'Susan',500),(1172,'Susan',499),
(1172,'Susan',498),(1173,'Joseph',499),(1173,'Joseph',498);
SELECT * FROM node;

Let's load it

mysql> DROP DATABASE IF EXISTS doub1ejack;
Query OK, 1 row affected (0.01 sec)

mysql> CREATE DATABASE doub1ejack;
Query OK, 1 row affected (0.00 sec)

mysql> USE doub1ejack
Database changed
mysql> CREATE TABLE node
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     nid INT NOT NULL,
    ->     title VARCHAR(20),
    ->     dir_phys_category_tid INT NOT NULL,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.23 sec)

mysql> INSERT INTO node (nid,title,dir_phys_category_tid) VALUES
    -> (1171,'Toby',499),(1172,'Susan',500),(1172,'Susan',499),
    -> (1172,'Susan',498),(1173,'Joseph',499),(1173,'Joseph',498);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>

Let's look at it

mysql> SELECT * FROM node;
+----+------+--------+-----------------------+
| id | nid  | title  | dir_phys_category_tid |
+----+------+--------+-----------------------+
|  1 | 1171 | Toby   |                   499 |
|  2 | 1172 | Susan  |                   500 |
|  3 | 1172 | Susan  |                   499 |
|  4 | 1172 | Susan  |                   498 |
|  5 | 1173 | Joseph |                   499 |
|  6 | 1173 | Joseph |                   498 |
+----+------+--------+-----------------------+
6 rows in set (0.00 sec)

mysql>

Next, let's run the proposed query

mysql> SELECT C.* FROM
    -> (SELECT nid,COUNT(1) fullcount FROM node GROUP BY nid) A
    -> INNER JOIN
    -> (SELECT nid,COUNT(1) goodcount FROM node
    -> WHERE dir_phys_category_tid IN (498,499)
    -> GROUP BY nid) B USING (nid)
    -> INNER JOIN
    -> (SELECT DISTINCT nid,title FROM node) C USING (nid)
    -> WHERE fullcount=goodcount;
+------+--------+
| nid  | title  |
+------+--------+
| 1171 | Toby   |
| 1173 | Joseph |
+------+--------+
2 rows in set (0.00 sec)

mysql>

OK why does it work ? Let's add the count columns, remove the WHERE and see

mysql> SELECT C.*,fullcount,goodcount FROM
    -> (SELECT nid,COUNT(1) fullcount FROM node GROUP BY nid) A
    -> INNER JOIN
    -> (SELECT nid,COUNT(1) goodcount FROM node
    -> WHERE dir_phys_category_tid IN (498,499)
    -> GROUP BY nid) B USING (nid)
    -> INNER JOIN
    -> (SELECT DISTINCT nid,title FROM node) C USING (nid)
    -> ;
+------+--------+-----------+-----------+
| nid  | title  | fullcount | goodcount |
+------+--------+-----------+-----------+
| 1171 | Toby   |         1 |         1 |
| 1172 | Susan  |         3 |         2 |
| 1173 | Joseph |         2 |         2 |
+------+--------+-----------+-----------+
3 rows in set (0.00 sec)

mysql>

Here is the correlation: When the number of categories (fullcount) is equal to the number of categories which are 498 and/or 499 (goodcount), then that row's nid is acceptable.

Give it a Try !!!

MySQL 5.5 – Get Minimum Value from Multiple Tables

Assuming a Staff table, it would be a query similar to this:

SELECT 
    s.*,                         -- needed columns from Staff table
    MIN(d.zipcodedistMiles) AS Miles_From_ZIP
FROM 
    Staff AS s
  LEFT JOIN
    tblZipCodeServed AS z
      ON  z.zipcodeservedStaffID = s.StaffID
  LEFT JOIN
    tblZipCodeDist AS d
      ON  d.zipcodedistStartZip = z.zipcodeservedZipCode
      AND d.zipcodedistEndZip = @TargetZipCode
GROUP BY
    s.StaffID ;                  -- the Primary Key of Staff

Best Answer

Related Solutions

Select Records Without Associations Outside a Certain List in MySQL

MySQL 5.5 – Get Minimum Value from Multiple Tables

Related Question