CODEWITHSUNDEEP
mysql
Vaeianor
Vaeianor

Reputation: 179

Mysql query: find rows where one column contains same data in more than one record while another column contains data that only occurs once

rowid |zipcode  | pid
------|---------|---------------------
1     |14586    | aaaaa
2     |14586    | aaaaa
3     |99001    | bbbbb
4     |99002    | bbbbb
5     |99002    | bbbbb
6     |10006    | ccccc
7     |10007    | ccccc
8     |10008    | ddddd
9     |10009    | eeeee

I'd like to find the rows where a pid occurs more than once and a zipcode occurs only once. For example, I want the query result to be : 

rowid |zipcode  | pid
------|---------|---------------------
3     |99001    | bbbbb
6     |10006    | ccccc
7     |10007    | ccccc

What the mysql query statement should look like?

Thanks in advance!

Upvotes: 0

Views: 97

Answers (2)

Vaeianor
Vaeianor

Reputation: 179

Finally figured out a working query based on Seb's answer

SELECT * FROM 
            (SELECT rowid,zipcode,pid
            FROM table
            GROUP BY pid,zipcode
            HAVING count(*) = 1) as a
JOIN (
SELECT rowid FROM table 
    where pid IN (
    Select * from (
        SELECT pid
        FROM table 
        GROUP BY pid
        HAVING COUNT(*) >1
    ) as b)) as c
ON a.rowid = c.rowid

Upvotes: 0

Seb
Seb

Reputation: 1551

You need to combine a few things here. First you need all zip codes with count 1, than all pid with count > 1. And they need to be in the same row.

SELECT * FROM 
(SELECT MIN(row_id) as use_row, zipcode, count(zipcode) as zip_count
FROM table
GROUP BY zipcode
HAVING zip_count = 1) as a
JOIN 
( SELECT MIN(row_id) as use_row, pid, count(pid) as pid_count
  FROM table
  GROUP BY pid
  HAVING pid_count > 1) as b 
  ON b.use_row=a.use_row

Hope that row-matching with MIN() works.

Upvotes: 1

Related Questions