Select same records that only have different values for a particular column

distinctoracleselect

Below is an example of my customer table. There some records having multiple values in BIRTHDAY DATE (by mistake or so). I only want to select those records that have same values for LASTNAME, MIDDLENAME, FIRSTNAME, SSN but different BIRTHDAY:

Member table

LASTNAME   MIDDLENAME   FIRSTNAME    SSN      BIRTHDAY
Jones      M            Carol        1234     17-DEC-45
Jones      M            Carol        1234     17-DEC-45
Jones      M            Carol        4425     20-APR-70
Black      S            Ted          5555     15-MAY-57
Roberts    T            Cole         1412     14-MAY-57
Roberts    T            Cole         1412     20-OCT-57
Roberts    S            Cole         1412     15-MAY-57

I would like the result to be:

LASTNAME   MIDDLEANME  FIRSTNAME    SSN      BIRTHDAY
Roberts    T           Cole         1412     14-MAY-57
Roberts    T           Cole         1412     20-OCT-57

Notice that there were few accounts with same SSN or full name in the table, they are not selected since they don't have everything same. Also Jones M. Carol with 1234 as SSN is not selected either since she does not have different Birthday for two different account.

This is my SQL query that I have so far and this is not working well necessarily.

SELECT x.FIRST_NM, x.MDL_NM, x.LAST_NM, x.SSN, x.BRTH_DT
FROM Member_table x
WHERE EXISTS
(
    SELECT FIRST_NM, MDL_NM, LAST_NM, SSN, COUNT(*)
    from Member_table
    WHERE CURRENT_RECORD_IN = 'Y'
    group by FIRST_NM, MDL_NM, LAST_NM, SSN
    having count(distinct BRTH_DT) > 1
)
ORDER BY FIRST_NM ASC, LAST_NM ASC, MDL_NM ASC, SSN ASC;

Any advice for this query?

Best Answer

Here's an example of using EXISTS and a correlated subquery. I tested on SQL Server, but will probably work on other RDBMS's.

drop table if exists table1
CREATE TABLE Table1
    (LASTNAME varchar(7), MIDDLENAME varchar(1), FIRSTNAME varchar(5), SSN int, BIRTHDAY varchar(9))
;

INSERT INTO Table1
    (LASTNAME, MIDDLENAME, FIRSTNAME, SSN, BIRTHDAY)
VALUES
    ('Jones', 'M', 'Carol', 1234, '17-DEC-45'),
    ('Jones', 'M', 'Carol', 1234, '17-DEC-45'),
    ('Jones', 'M', 'Carol', 4425, '20-APR-70'),
    ('Black', 'S', 'Ted', 5555, '15-MAY-57'),
    ('Roberts', 'T', 'Cole', 1412, '14-MAY-57'),
    ('Roberts', 'T', 'Cole', 1412, '20-OCT-57'),
    ('Roberts', 'S', 'Cole', 1412, '15-MAY-57')
;


SELECT *
FROM table1 t1
WHERE EXISTS (
        SELECT *
        FROM table1
        WHERE LASTNAME = t1.LASTNAME
            AND MIDDLENAME = t1.MIDDLENAME
            AND FIRSTNAME = t1.FIRSTNAME
            AND SSN = t1.SSN
            AND BIRTHDAY <> t1.BIRTHDAY
        )

| LASTNAME | MIDDLENAME | FIRSTNAME | SSN  | BIRTHDAY  |
|----------|------------|-----------|------|-----------|
| Roberts  | T          | Cole      | 1412 | 14-MAY-57 |
| Roberts  | T          | Cole      | 1412 | 20-OCT-57 |

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 Multiple Values from Same Column; one sql statment

Just use GROUP_CONCAT with a WHERE condition

SELECT user_id,GROUP_CONCAT(meta_value ORDER BY id) 
FROM t 
WHERE meta_key  IN('first_name','last_name','street_add','city','state') 
GROUP BY user_id

Best Answer

Related Solutions

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

Mysql – Select Multiple Values from Same Column; one sql statment

Related Question