Select only those records that have different/multiple values for a particular column

countdistinctselect

Below is an example of my membership table. There some records having multiple values in the email field. I only want to select those records that have multiple email values:

Member table

ID   LASTNAME    FIRSTNAME    EMAIL
567  Jones       Carol        carolj@gmail.com
567  Jones       Carol        caroljones@aol.com
678  Black       Ted          tedblack@gmail.com
908  Roberts     Cole         coleroberts@gmail.com
908  Roberts     Cole         coler@aol.com
908  Roberts     Cole         colerobersc@hotmail.com

I would like the result to be:

567  Jones       Carol        carolj@gmail.com
567  Jones       Carol        caroljones@aol.com
908  Roberts     Cole         coleroberts@gmail.com
908  Roberts     Cole         coler@aol.com
908  Roberts     Cole         colerobersc@hotmail.com

Notice that Ted Black is missing because he only has one entry for email address.

I should clarify that my membership table has more than 4 columns. There are additional columns for phone and address, etc. And there could be multiple entries for a member because he/she has more than one phone number or address. I only want to capture those individuals who have multiple email addresses.

This is part of a database cleanup and a primary key will be added. I should clarify further that some persons could have multiple entries with same email address. At this phase I don't want to capture those multiple entries with the same email address but only those have who multiple entries with different email address.

Best Answer

You could do something like:

select distinct x.id, x.lastname, x.firstname, x.email
from t as x
join (
    select id
    from t
    group by id
    having count(distinct email) > 1
) as y
    on x.id = y.Id

Related Solutions

Mysql – Select records that do not have associations outside a certain list

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 – select all records for those persons who have one or more entries that equals zero dues

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

Member table

Best Answer

Related Solutions

Mysql – Select records that do not have associations outside a certain list

Mysql – select all records for those persons who have one or more entries that equals zero dues

Related Question